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»»

how to split the comma delimited string into rows without using LOOP, CTE, XML. Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 5:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
DECLARE @PARAMETER NVARCHAR(MAX)
SELECT @P = 'MONDAY,SUN,FUN,D'
Post #1503041
Posted Wednesday, October 9, 2013 8:41 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: Yesterday @ 7:22 PM
Points: 3,637, Visits: 7,935
Why do you want to limit the methods? You might be losing performance over strange requirements.
You could use CLR, but I'm not sure if that counts as a loop for you.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503141
Posted Wednesday, October 9, 2013 9:00 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Use Jeff's string splitter function?

Tally OH! An Improved SQL 8K “CSV Splitter” Function


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


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1503158
Posted Wednesday, October 9, 2013 9:04 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: Yesterday @ 7:22 PM
Points: 3,637, Visits: 7,935
Abu Dina (10/9/2013)
Use Jeff's string splitter function?

Tally OH! An Improved SQL 8K “CSV Splitter” Function


Jeff's string splitter uses CTEs, but that would have been my first suggestion.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1503161
Posted Wednesday, October 9, 2013 11:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
Why did you open another thread?
What about the answers already provided here?




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1503230
Posted Wednesday, October 9, 2013 11:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 1,920, Visits: 19,338
LutzM (10/9/2013)
Why did you open another thread?
What about the answers already provided here?


that post was to split into columns.....now it seems into rows.....


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1503232
Posted Wednesday, October 9, 2013 12:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
J Livingston SQL (10/9/2013)
LutzM (10/9/2013)
Why did you open another thread?
What about the answers already provided here?


that post was to split into columns.....now it seems into rows.....


Yep, you're right.
I must have missed the sample data and expected result provided by the OP in both threads. Therefore missing the obvious.

I'd say both requirements most probably can be transformed into each other using pivot (or CrossTab, as I'd prefer) or unpivot of the "other" result set.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1503235
Posted Friday, October 11, 2013 4:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:57 AM
Points: 55, Visits: 185
-- f.e.: to split the comma delimited string into rows without using LOOP, CTE, XML.
declare @p nvarchar(max) = '', @start datetime
select @p += name + ',' from master.sys.sysobjects
select items = @@rowcount + 1
select @p += 'End of Teststring'
select lentotal = len(@p)
print 'Teststring: ''' + @p + ''''
select @start = getdate()
;with zahlen(n) as (select 1 union all select n + 1 from zahlen where n < datalength(@p))
select
--n = case when n = 1 then 0 else n + 1 end, -- start delimiter found
--m = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)), -- calulated pos of next delimiter
--l = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p))
-- - n - case when n = 1 then 0 else 1 end, -- calulated len to next delimiter
[substring] = substring(@p,
case when n = 1 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 1 then 1 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token
)
from zahlen where n = 1 or substring(@p, n, 1) = ','
option (maxrecursion 0);
print 'time used ' + cast(cast(datediff(ms, @start, getdate()) / 1000.0 as decimal(16,2)) as varchar(8))
+ ' seconds for ' + cast(@@rowcount as varchar(8)) + ' rows'
Post #1503937
Posted Friday, October 11, 2013 4:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 13,730, Visits: 10,683
Th. Fuchs (10/11/2013)
-- f.e.: to split the comma delimited string into rows without using LOOP, CTE, XML.
declare @p nvarchar(max) = '', @start datetime
select @p += name + ',' from master.sys.sysobjects
select items = @@rowcount + 1
select @p += 'End of Teststring'
select lentotal = len(@p)
print 'Teststring: ''' + @p + ''''
select @start = getdate()
;with zahlen(n) as (select 1 union all select n + 1 from zahlen where n < datalength(@p))
select
--n = case when n = 1 then 0 else n + 1 end, -- start delimiter found
--m = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)), -- calulated pos of next delimiter
--l = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p))
-- - n - case when n = 1 then 0 else 1 end, -- calulated len to next delimiter
[substring] = substring(@p,
case when n = 1 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 1 then 1 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token
)
from zahlen where n = 1 or substring(@p, n, 1) = ','
option (maxrecursion 0);
print 'time used ' + cast(cast(datediff(ms, @start, getdate()) / 1000.0 as decimal(16,2)) as varchar(8))
+ ' seconds for ' + cast(@@rowcount as varchar(8)) + ' rows'


Aside from the fact that my eyes hurt of that code formatting (there's a SQL code IFCode shortcut at the left, you know), there's still a CTE in there.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1503944
Posted Friday, October 11, 2013 5:05 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 37,075, Visits: 31,636
Th. Fuchs (10/11/2013)
-- f.e.: to split the comma delimited string into rows without using LOOP, CTE, XML.
declare @p nvarchar(max) = '', @start datetime
select @p += name + ',' from master.sys.sysobjects
select items = @@rowcount + 1
select @p += 'End of Teststring'
select lentotal = len(@p)
print 'Teststring: ''' + @p + ''''
select @start = getdate()
;with zahlen(n) as (select 1 union all select n + 1 from zahlen where n < datalength(@p))
select
--n = case when n = 1 then 0 else n + 1 end, -- start delimiter found
--m = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)), -- calulated pos of next delimiter
--l = isnull(nullif(charindex(',', @p, n + 1), 0), len(@p))
-- - n - case when n = 1 then 0 else 1 end, -- calulated len to next delimiter
[substring] = substring(@p,
case when n = 1 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 1 then 1 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token
)
from zahlen where n = 1 or substring(@p, n, 1) = ','
option (maxrecursion 0);
print 'time used ' + cast(cast(datediff(ms, @start, getdate()) / 1000.0 as decimal(16,2)) as varchar(8))
+ ' seconds for ' + cast(@@rowcount as varchar(8)) + ' rows'


That uses a recursive CTE that counts. First, the OP wanted to do it without the use of a CTE. Second, please see the following article for why you shouldn't use rCTEs that count.
http://www.sqlservercentral.com/articles/T-SQL/74118/


--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 #1503951
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse