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='' /><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 />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

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
*/

declare@i int ,
@j int ,
@delimUsed varchar(11) ,
@s varchar(4000)

select @i = 1
while@fldnum > 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)
return@s
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