Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

To split comma delimiterd string Expand / Collapse
Author
Message
Posted Sunday, May 3, 2009 10:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 13, 2010 2:23 AM
Points: 129, Visits: 236
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
Post #709170
Posted Sunday, May 3, 2009 10:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 10, 2009 9:50 PM
Points: 3, Visits: 14
Its always better to write a Table Valued Function that would return a table in the desired format.
Post #709173
Posted Monday, May 4, 2009 12:24 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Please see this article by some really smart guy who hangs out here.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #709200
Posted Monday, May 4, 2009 12:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
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
slno name1
1 1
2 jack~2
3 joy~2
4 rozy~3
5 X~4
6 YYY~5

for removing the '~'

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

select * from #temp
slno name1
1 1
2 jack
3 joy
4 rozy
5 X
6 YYY

ARUN SAS
Post #709201
Posted Monday, May 4, 2009 1:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
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 here

Post #709211
Posted Monday, May 4, 2009 1:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
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 here

Post #709212
Posted Tuesday, December 22, 2009 11:54 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 12:12 AM
Points: 10, Visits: 44
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
Post #838382
Posted Thursday, July 1, 2010 7:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #946562
Posted Thursday, July 1, 2010 7:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 36,995, Visits: 31,517
RBarryYoung (5/4/2009)
Please see this article 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #946563
Posted Saturday, August 28, 2010 7:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Jeff Moden (7/1/2010)
RBarryYoung (5/4/2009)
Please see this article 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 ).


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #976925
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse