SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to split the comma delimited string into rows without using LOOP, CTE, XML.


how to split the comma delimited string into rows without using LOOP, CTE, XML.

Author
Message
mynkdby
mynkdby
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 99
DECLARE @PARAMETER NVARCHAR(MAX)
SELECT @P = 'MONDAY,SUN,FUN,D'
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40696 Visits: 19815
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Abu Dina
Abu Dina
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2877 Visits: 3325
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40696 Visits: 19815
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22927 Visits: 13559
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
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11803 Visits: 37484
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

LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22927 Visits: 13559
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
Th. Fuchs
Th. Fuchs
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 191
-- 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'
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60435 Visits: 13297
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208397 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search