[Resolved] Substring Field with a Delimiter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Substring Field with a Delimiter

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-04-11 : 09:37:40

Have following query that is working fine:

DECLARE @SQLSTRING NVARCHAR(4000) 

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''SELECT   
           yaalph
from       vgiprdhrp.f060116a
'')'

exec (@SQLSTRING)

Result is (Last and First Name in same field):

Alvarez, Saul
Hernandez, Frank G.
Baloy, Jose
Iglesias, Julio

I need to split the result into 2 fields respective last name and first name. How can I do this? Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-11 : 09:42:03

This will work on the data set you provided

declare @str varchar(50)
set @str = 'Hernandez, Frank G. '
select LEFT(@str,CHARINDEX(',',@str)-1),right(@str,CHARINDEX(',',@str))

Jim

Everyday I learn something that somebody else already knew

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-04-11 : 13:16:57

Jim,

Using the idea from your post, I had to change the syntax to conform to AS400 / iSeries / db2 and it is working. Thank you.

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''SELECT   
           substr(yaalph, 1, (locate('''','''', yaalph) - 1)) as LastName,
           substr(yaalph, (locate('''','''', yaalph) + 1)) as FirstName
from       vgiprdhrp.f060116a
where yapast = ''''0'''' and yaalph <> '''' ''''
'')'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources