Parse Delimited String -tsql help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Parse Delimited String -tsql help

Hi, I wanted to be able to parse a string in a column that is delimited by a ” character. Didn’t want too much bulk code, and not sure if this is possible in one statement. I’ve tried CHARINDEX, but maybe there are easier or better ways? It should have this logic:
say string = MyGenreMyClassMySuBCategory select X as Position1, Y as Position2, Z as Position3 Postion1 Position2 Position3
My Genre MyClass MySuBCategory Thx for help in advance!
Use CHARINDEX with SUBSTRING function to get the desired output.. MohammedU.
you can make use of various string splitting function here
Thanks khtan, that’s exactly what I’m looking for – although I’d like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value). Can you or anyone give some help on that?
I’d like to see: DValue1,DValue2,DValue3,Field1, Field2, (etc….) The function is below. CREATE FUNCTION dbo.Split
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
RETURNS @RtnValue table
Id int identity(1,1),
Data nvarchar(100)
Declare @Cnt int
Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0)
Insert Into @RtnValue (data)
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData)) Return

"although I’d like to change the function a little to return it each delimited value as a column (for each record) rather than a table (multiple rows for each value)" What do you mean by this ?
See if it works for you<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />declare @sql Varchar(1000)<br />select @sql = ‘MyGenreMyClassMySuBCategory'<br />select @sql = ‘select ‘+char(39)+replace(@sql, ”, ”’,”’)+char(39)<br />exec( @sql)<br /><br /><br />MohammedU.<br />Moderator<br />
Thx, MohammedU – this may work, but can you tell me how to incorporate this into a more complex sql query.
What I mean is rather than {select @sql = ‘MyGenreMyClassMySuBCategory’} I want to substiture the column name of the table to parse, rather than give it a static value. Will this work? Edit: Actually I tried this as test without the dynamic sql part, and it doesn’t give desired result. Although it replaces it into comma delimited, I wanted to represent each part as a column. Thx
Uhoh, that sounds like a case for
It’s a long article, but well worth reading. And while you’Re at Erlands’ site, you might also want to check out his article on arrays and lists in SQL. —
Frank Kalis
Microsoft SQL Server MVP
Ok, ALL – one more bit of help needed: I’ve chosen this function below, which suits me fine:
However I’d like a way to modify it so I can return a null or empty string if the fieldnum I pass in doesn’t exist. As it currently stands, it returns an invalid parameter passed error. Some data in the field has ‘ac’ and others have ‘a’ only, so if I try to specify field3 when only ‘a’ exists, I get the error. Any ideas?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetEntryDelimiitted]’) and xtype in (N’FN’, N’IF’, N’TF’))
drop function [dbo].[f_GetEntryDelimiitted]
GO Create function f_GetEntryDelimiitted
@line varchar(4000) ,
@fldnum int ,
@delim varchar(10) ,
@quoted varchar(1)– Y/N
returns varchar(400)
create table #a (s varchar(1000))
insert #a select ‘"John","Smith","IT",2,"sql",1’
insert #a select ‘"Fred","Jones","Assassin",7,"Guns",5’
insert #a select ‘"Peter","Brown","Politics",23,minister,5’ select a.* from
select Forname= dbo.f_GetEntryDelimiitted (s, 1, ‘,’, ‘Y’) ,
Surname = dbo.f_GetEntryDelimiitted (s, 2, ‘,’, ‘Y’) ,
Profession = dbo.f_GetEntryDelimiitted (s, 3, ‘,’, ‘Y’) ,
ProfessionExp = dbo.f_GetEntryDelimiitted (s, 4, ‘,’, ‘Y’) ,
Skill = dbo.f_GetEntryDelimiitted (s, 5, ‘,’, ‘Y’) ,
SkillExp = dbo.f_GetEntryDelimiitted (s, 6, ‘,’, ‘Y’)
from #a
) a drop table #a
*/ [email protected] int ,
@j int ,
@delimUsed varchar(11) ,
@s varchar(4000) select @i = 1
[email protected] > 0
select @fldnum = @fldnum – 1
if substring(@line, @i, 1) = ‘"’ and @Quoted = ‘Y’
begin select @delimUsed = ‘"’ + @Delim ,
@i = @i + 1
select @delimUsed = @Delim
end select @j = charindex(@delimUsed, @line, @i)
if @j = 0
select @j = datalength(@line) + 1
if @fldnum > 0
select @i = @j +len(@delimused)
end select @s = substring(@line, @i, @j – @i)
[email protected]
end go

Oh, Nigel’s function. You can change select @s = substring(@line, @i, @j – @i) to
select @s = case when @j > @i then substring(@line, @i, @j – @i)
else NULL
end KH