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 Friday, October 11, 2013 5:10 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
mynkdby (10/9/2013)
DECLARE @PARAMETER NVARCHAR(MAX)
SELECT @P = 'MONDAY,SUN,FUN,D'


In order to help with this, you need to answer a serious question. Why must it be without a loop or CTE?


--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 #1503952
Posted Friday, October 11, 2013 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
Jeff Moden (10/11/2013)
mynkdby (10/9/2013)
DECLARE @PARAMETER NVARCHAR(MAX)
SELECT @P = 'MONDAY,SUN,FUN,D'


In order to help with this, you need to answer a serious question. Why must it be without a loop or CTE?



Jeff i am just exploring things to learn more, not any specific reason behind that we cant use loop,xml and CTE.
and i came across with the solution like this.. i am replacing the comma with ’ UNION ALL SELECT ‘ (Single Quote & “UNION ALL SELECT” & Single Quote) and that works.

Thanks jeff making me aware of the fact that code like that are VERY prone to SQL Injection. I would appreciate it if you could enlighten me on this more.

Thanks in Advance
Post #1503990
Posted Friday, October 11, 2013 7:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
For more information on SQL Injection, my recommendation would be to Google it. It's a large subject and contains way too much information for me to post on a thread.

The bottom line is that if you concatenate any character based parameters using Dynamic SQL, your code is subject to SQL Injection. That also includes any front-end code. In order to prevent such injection, the code must be properly parameterized. In SQL Server, such parameterization can be done using sp_ExecuteSQL.

Probably the best example on the internet of how to use that for "catch-all" queries may be found at the following link.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Probably the world's most famous and extremely pertinent cartoon on the subject of SQL Injectio can be found at the following URL...
http://xkcd.com/327/

SQL Injection is still the world's biggest problem for hack-attacks. Don't take it lightly.


--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 #1504015
Posted Sunday, October 13, 2013 7:23 PM


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 @ 9:53 PM
Points: 3,438, Visits: 5,390
Abu Dina (10/9/2013)
Use Jeff's string splitter function?

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


If you take this suggestion, be sure to change your data type from NVARCHAR(MAX) to VARCHAR(8000).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1504307
Posted Monday, October 14, 2013 12:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:05 AM
Points: 55, Visits: 186

---- drop the recursion into a static table
if object_id('dbo._numbers') is null --- drop table dbo._numbers
begin
print 'create static collection of numbers'
create table dbo._numbers (n integer primary key(n))
declare @i integer = 0
set nocount on
while @i <= 214748 --3647 -- the hidden recursion
begin insert into dbo._numbers(n) values(@i) select @i += 1 end
end

select @start = getdate()
select [substring] = substring(@p,
case when n = 0 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 0 then 0 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token - len(delimiter)
)
from dbo._numbers where n = 0 or substring(@p, n, 1) = ','


---- or hide recursion in the stack (attention, max 31 item pssible)
create function dbo.stt(@source varchar(8000)) returns @t table (t varchar(8000)) as
begin
declare @token varchar(8000), @l integer
select @l = @@nestlevel
if @source like '%,%'
begin -- token exists
select @token = left(@source, charindex(',', @source) -1) -- cut first token
select @source = right(@source, len(@source) - len(@token) -1) -- trimm tail
insert into @t(t) values(@token) -- the one token found into resultset
insert into @t(t) select t from dbo.stt(@source) -- the same procedure for the rest THE RECURSION
end -- first token cutted
else
begin -- last token found
insert into @t(t) values(@source) -- the last feather
end -- now ready
return
end
Post #1504329
Posted Tuesday, October 15, 2013 7:38 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
Th. Fuchs (10/14/2013)

---- drop the recursion into a static table
if object_id('dbo._numbers') is null --- drop table dbo._numbers
begin
print 'create static collection of numbers'
create table dbo._numbers (n integer primary key(n))
declare @i integer = 0
set nocount on
while @i <= 214748 --3647 -- the hidden recursion
begin insert into dbo._numbers(n) values(@i) select @i += 1 end
end

select @start = getdate()
select [substring] = substring(@p,
case when n = 0 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 0 then 0 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token - len(delimiter)
)
from dbo._numbers where n = 0 or substring(@p, n, 1) = ','


---- or hide recursion in the stack (attention, max 31 item pssible)
create function dbo.stt(@source varchar(8000)) returns @t table (t varchar(8000)) as
begin
declare @token varchar(8000), @l integer
select @l = @@nestlevel
if @source like '%,%'
begin -- token exists
select @token = left(@source, charindex(',', @source) -1) -- cut first token
select @source = right(@source, len(@source) - len(@token) -1) -- trimm tail
insert into @t(t) values(@token) -- the one token found into resultset
insert into @t(t) select t from dbo.stt(@source) -- the same procedure for the rest THE RECURSION
end -- first token cutted
else
begin -- last token found
insert into @t(t) values(@source) -- the last feather
end -- now ready
return
end


If you read the title of this post, the OP wanted to be able to do this without a loop or CTE. The first script you have above uses a loop. The second script avoids an explicit loop but has the same problem as a loop insofar as being RBAR, not to mention the recursive call limit you identified.


--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 #1504752
Posted Tuesday, October 15, 2013 7:55 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
@mynkdby,

I've been trying to figure out why you wanted to avoid a CTE for this and I believe I've come up with the answer. Since you're on a quest for knowledge, I thought I'd throw some additional info at you on the subject.

There are two different types of CTEs... recursive and non-recursive. Recursive CTEs (rCTE) that count out a sequence of numbers are horrible for performance and resource usage. You can find out more about that in the following article.
Hidden RBAR: Counting with Recursive CTE's


The bottom line is that you should pretty much never use a recursive CTE that counts.

Then, there's a form of non-recursive CTE first documented by Itzik Ben-Gan. I don't know what anyone else calls it but I call it a "Cascading CTE" or "cCTE" for short. This is a very high performance, low resource usage method for generating sequences of numbers for splitting delimited values and a whole bunch of other uses. You can see such a cCTE in action in the DelimitedSplit8K function that you were previously directed to. It's nearly as fast as using a Tally Table and uses zero reads. You can find out more about both a cCTE and a Tally Table and how they are both high performance replacements for certain types of loops in the following article. DO read the intro to that article which explains that the "splitter method" in the article is ineffecient when it comes to splitting delimited values but was included because it easily explains how a Tally Table works.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.


Getting back to the original subject, it's flat out dangerous to create dynamic SQL from character based parameters that came from "the public" because those parameters can easily contain SQL Injection methods as I previously demonstrated on your other thread. If you haven't Googled "SQL Injection" and done a bit of a study on the subject, you could be leading the folks you're working for into a hack-attack.

If you have any other questions, please don't hesitate to ask.


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

Add to briefcase ««12

Permissions Expand / Collapse