July 19, 2012 at 1:09 am
Comments posted to this topic are about the item get all values separated by commas
July 19, 2012 at 1:37 am
Neat use of update. This can also be done using the FOR XML construct:
select SUBSTRING((SELECT (',' + id) FROM teste ORDER BY id FOR XML PATH('') ), 2, 1000)
July 19, 2012 at 4:10 am
nice, but lookout to the substring length, as i have to lookout for the nvarchar max length.
July 19, 2012 at 7:17 am
stuff((select ',' + ID
from teste
for XML path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'
),
1,
1,
'')
This copes with any string length.
July 19, 2012 at 8:11 am
Hi-
How about something like the following. It may have some bugs but can be used as a starting point.
drop procedure sp_splitIntoIndividualComponents
go
create procedure sp_splitIntoIndividualComponents
As
declare @STR varchar(200)
set @STR = '2,3,4,5,6,7,8,9,'
;With t1(n1, str1, str2, n)
as
(
select1,convert(varchar(200), (substring(@str, 0, charIndex(',', @STR)))) str1,
convert(varchar(200), (substring(@str, charIndex(',', @STR) + 1, 20))) str2,
charIndex(',', @STR) as n
union all
select2,convert(varchar(200), (substring(t1.str2, 0, charIndex(',', t1.str2)))) str1,
convert(varchar(200), (substring(t1.str2, charIndex(',', t1.str2) + 1, 20))) str2,
charIndex(',', t1.str2) as n
from t1 where CHARINDEX(',', t1.str2) > 0
union all
select3,convert(varchar(200), t1.str2) str1,
'' str2,
charIndex(',', t1.str2) as n
from t1 where CHARINDEX(',', t1.str2) = 0 and (LEN(t1.str1) > 0 Or LEN(t1.str2) > 0)
)
select t1.str1 into #t from t1 where t1.str1 <> ''
select * from #t
go
exec sp_splitIntoIndividualComponents
go
July 19, 2012 at 8:16 am
July 19, 2012 at 9:23 am
Arnold Lieberman (7/19/2012)
stuff((select ',' + IDfrom teste
for XML path(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'
),
1,
1,
'')
This copes with any string length.
ok... i loved the for XML path.
The stuff it's kind of cute, but i didn't understand TYPE... and the ./text thing... I have to study more.
Where can I learn more about this stuff?
I only have the 2778 MS Quering SQL server 2005 lessons's in the bag, and a little experience.
Thanks
July 19, 2012 at 11:02 am
ok... i loved the for XML path.
The stuff it's kind of cute, but i didn't understand TYPE... and the ./text thing... I have to study more.
Where can I learn more about this stuff?
I only have the 2778 MS Quering SQL server 2005 lessons's in the bag, and a little experience.
Thanks
Well, there's a fantastic website called SQL Server Central which I learned a lot from, and continue to do so!
Most programmers who come from the usual programming languages have a hard time using tsql in the best manner. Declarative programming requires a completely different mindset to solving problems. My general rule of thumb is that if you have to use a loop or a cursor then you're doing it the wrong way 😀
July 19, 2012 at 2:28 pm
Fewer steps and converts nulls to blanks if you want them (take out the ISNULL operator if you don't want null values). You can also easily make the delimiter a defined variable.
--Create a test table
CREATE TABLE dbo.Test (testval VARCHAR(10))
GO
--Insert values for testing
INSERT INTO dbo.Test (testval) VALUES ('T1')
INSERT INTO dbo.Test (testval) VALUES ('T2')
INSERT INTO dbo.Test (testval) VALUES (NULL)
INSERT INTO dbo.Test (testval) VALUES ('T3')
GO
DECLARE @i NVARCHAR(MAX)
SELECT @i = COALESCE(@i + ',','') + CAST((ISNULL(testval,'')) AS NVARCHAR(MAX))
FROM dbo.Test
SELECT @i
July 19, 2012 at 2:30 pm
This is a great idea.
Thanks for posting.
August 24, 2012 at 12:17 pm
Check the article http://www.sqlservercentral.com/articles/Tally+Table/72993/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply