To split comma delimiterd string

Question

Post reply

To split comma delimiterd string

ezhil-649633

SSC Eights!

Points: 975
More actions
May 3, 2009 at 10:19 pm

#204265

Hi,
Can u suggest be better way(based on performance) to split by comma delited string.
Input:
String - 1,'Joy'~2,'Jack'~3,'Rozy'
Expected Result:
As Table
ID Name
1 Joy
2 Jack
3 Rozy

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sidhartha.pooboni SSC Enthusiast Points: 149 More actions · Answer 1

Its always better to write a Table Valued Function that would return a table in the desired format.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Please see this article [/url]by some really smart guy who hangs out here.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

arun.sas SSChampion Points: 11831 More actions · Answer 3

ezhil (5/3/2009)
Hi,
Can u suggest be better way(based on performance) to split by comma delited string.
Input:
String - 1,'Joy'~2,'Jack'~3,'Rozy'
Expected Result:
As Table
ID Name
1 Joy
2 Jack
3 Rozy

Hi,

try this statement

create table #temp

(

slno int identity(1,1),

name1 varchar(100)

)

declare @abc varchar(1000)/*Alwayes should be in max value*/

select @abc = '1,joy~2,jack~2,rozy~3,X~4,YYY~5'

select @abc = 'select ''' + replace (@ABC,',',''' union select ''')+''''

insert into #temp (name1)

exec (@ABC)

select * from #temp

RESULT

slnoname1

11

2jack~2

3joy~2

4rozy~3

5X~4

6YYY~5

for removing the '~'

update #temp

set name1 = left(name1,charindex('~',name1,0)-1) from #temp

where name1 like '%~%'

select * from #temp

slnoname1

11

2jack

3joy

4rozy

5X

6YYY

ARUN SAS

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

Try this Function

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- Select * From [dbo].[fnSplit] ('A,b' , ',')

ALTER FUNCTION [dbo].[fnSplit]

(@pString varchar(5000),@pSplitChar char(1))

returns @tblTemp table (tid int,value varchar(1000))

as

begin

declare @vStartPosition int

declare @vSplitPosition int

declare @vSplitValue varchar(1000)

declare @vCounter int

set @vCounter=1

select @vStartPosition = 1,@vSplitPosition=0

set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition)

if (@vSplitPosition=0 and len(@pString) != 0)

begin

INSERT INTO @tblTemp

(

tid ,

value

)

VALUES

(

1 ,

@pString

)

return --------------------------------------------------------------->>

end

set @pString=@pString+@pSplitChar

while (@vSplitPosition > 0 )

begin

set @vSplitValue = substring( @pString , @vStartPosition , @vSplitPosition - @vStartPosition )

set @vSplitValue = ltrim(rtrim(@vSplitValue))

INSERT INTO @tblTemp

(

tid ,

value

)

VALUES

(

@vCounter ,

@vSplitValue

)

set @vCounter=@vCounter+1

set @vStartPosition = @vSplitPosition + 1

set @vSplitPosition = charindex( @pSplitChar , @pString , @vStartPosition )

end

return

end

For Performance, this function can be useful and handy to be used with parameters with a limited number of List of values. Never Store such value in the database to avoid bottlenecks of string parsing.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 5

Sorry... Disn't told you the usage in your scenario,

here it is...

Declare @v1 varchar(20)

Declare @v2 varchar(1000)

Set @v1 = '1,Joy~2,Jack~3,Rozy'

Select Left([value],charindex(',',[value])-1) as ID,

right([value],len([value]) - charindex(',',[value])) as val

from dbo.fnSplit(@v1,'~')

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

rafidheenm SSC Veteran Points: 240 More actions · Answer 6

Hi ezhil,

please check this article ..

This article has two types of split.

1. XML split

2. SQL Function (Basic SQL string split for SQL 2000 or later)

http://www.sqlservercentral.com/articles/XML/66932/

With regards,

Rafidheen.M

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

rafidheenm (12/22/2009)
Hi ezhil,
please check this article ..
This article has two types of split.
1. XML split
2. SQL Function (Basic SQL string split for SQL 2000 or later)
http://www.sqlservercentral.com/articles/XML/66932/
With regards,
Rafidheen.M

Just saw this so sorry for the really late post on it. Splitting strings using XML is comparatively dog slow and shouldn't be used. Instead, use either a Tally/Numbers table splitter or a cteTally splitter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

RBarryYoung (5/4/2009)
Please see this article [/url]by some really smart guy who hangs out here.

Heh... I know it's been over a year and I'm sorry I missed this. Thanks for the nice compliment, Barry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Jeff Moden (7/1/2010)
RBarryYoung (5/4/2009)
Please see this article [/url]by some really smart guy who hangs out here.
Heh... I know it's been over a year and I'm sorry I missed this. Thanks for the nice compliment, Barry.

Heh, you're welcome, of course Jeff.

I actually dropped a bunch of those around over the years just to see how long it would take you to find them if I didn't use your name 😛 (but I did always link your articles or a post 😀 ).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

Heh... glad I'm not the only one behind on my posts. 🙂

Thanks again, Barry and good to "see" you again... you've been "hiding".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 11

Jeff Moden (8/28/2010)
Heh... glad I'm not the only one behind on my posts. 🙂
Thanks again, Barry and good to "see" you again... you've been "hiding".

I think he's been lurking behind the grassy knoll with a Pork chop launcher 😉

Nice to see you back Barry :w00t:

Far away is close at hand in the images of elsewhere.
Anon.