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.
Moderator
SQL-Server-Performance.com

you can make use of various string splitting function herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
KH

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)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
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
END

"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 ?
KH

See if it works for you<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> declare @sql Varchar(1000) select @sql = ‘MyGenreMyClassMySuBCategory' select @sql = ‘select ‘+char(39)+replace(@sql, ”, ”’,”’)+char(39) exec( @sql) MohammedU. Moderator SQL-Server-Performance.com

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 forhttp://www.sommarskog.se/dynamic_sql.html
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
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

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? http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html
—————————————————————-
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)
as
begin
/*
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
begin
select @fldnum = @fldnum – 1
if substring(@line, @i, 1) = ‘"’ and @Quoted = ‘Y’
begin select @delimUsed = ‘"’ + @Delim ,
@i = @i + 1
end
else
begin
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

]]>