October 9, 2013 at 5:58 am
DECLARE @PARAMETER NVARCHAR(MAX)
SELECT @P = 'MONDAY,SUN,FUN,D'
October 9, 2013 at 8:41 am
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.
October 9, 2013 at 9:00 am
Use Jeff's string splitter function?
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
---------------------------------------------------------
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[/url]
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
October 9, 2013 at 9:04 am
Abu Dina (10/9/2013)
Use Jeff's string splitter function?
Jeff's string splitter uses CTEs, but that would have been my first suggestion.:-)
October 9, 2013 at 11:50 am
Why did you open another thread?
What about the answers already provided here?
October 9, 2013 at 11:54 am
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
October 9, 2013 at 12:04 pm
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.
October 11, 2013 at 4:27 am
-- 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'
October 11, 2013 at 4:49 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 11, 2013 at 5:05 am
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
Change is inevitable... Change for the better is not.
October 11, 2013 at 5:10 am
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
Change is inevitable... Change for the better is not.
October 11, 2013 at 7:02 am
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
October 11, 2013 at 7:42 am
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...
SQL Injection is still the world's biggest problem for hack-attacks. Don't take it lightly.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2013 at 7:23 pm
Abu Dina (10/9/2013)
Use Jeff's string splitter function?
If you take this suggestion, be sure to change your data type from NVARCHAR(MAX) to VARCHAR(8000).
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 14, 2013 at 12:51 am
---- 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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply