I have a varchar @a='a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p', which has | delimited values. I want to split this variable in a array or a table.
How can I do this?
-
1stackoverflow.com/questions/314824/…, stackoverflow.com/questions/697519/…Daniel Vassallo– Daniel Vassallo2010-05-07 05:26:27 +00:00Commented May 7, 2010 at 5:26
-
possible duplicate of stackoverflow.com/questions/2647/split-string-in-sqlMatt Hamilton– Matt Hamilton2010-05-07 05:30:53 +00:00Commented May 7, 2010 at 5:30
Add a comment
|
4 Answers
Use a table valued function like this,
CREATE FUNCTION Splitfn(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
and get your variable and use this function like this,
SELECT i.items FROM dbo.Splitfn(@a,'|') AS i
4 Comments
ACP
@gbn does table of numbers execute faster than a loop.... Please explain a bit more...
gbn
Please see my links in my answer
Manuel Castro
There are more elegant solutions out there but when supporting legacy code bases from SQL 2005 and below this is the only choice
nulltron
should you cleanup the temp table that is returned from the function?
In general, this is such a common question here
I'll give the common answer: Arrays and Lists in SQL Server 2005 and Beyond by Erland Sommarskog
I'd recommend a table of numbers, not a loop, for general use.
1 Comment
jumxozizi
Perhaps you could improve the answer by explaining how to apply this method to split a text at each delimiter. =)
Try this one:
declare @a varchar(10)
set @a = 'a|b|c|'
while len(@a) > 1
begin
insert into #temp
select substring(@a,1,patindex('%|%',@a)-1);
set @a = substring(@a,patindex('%|%',@a)+1,len(@a))
end;
2 Comments
Alexey Shcherbak
Nice solution, but need to clarify - it's important that string should be ended with delimiter symbol. If it won't - you will fall into infinite cycle
Alexey Shcherbak
my extension to your solution is to add this clause after your "set @a" row: IF(patindex('%|%',@a)=0) BEGIN INSERT INTO #temp select @a; BREAK; END;
Here's an alternative XML based solution. It seems to have a similar performance as the Splitfn() solution.
This converts varchar a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p into XML <a>a</a><a>b</a><a>c</a><a>d</a><a>e</a><a>f</a><a>g</a><a>h</a><a>i</a><a>j</a><a>k</a><a>l</a><a>m</a><a>n</a><a>o</a><a>p</a> and extracts the value from each XML <a> node.
declare @a varchar(max);
set @a = 'a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p';
declare @xml xml;
set @xml
= '<a>'+replace(replace(replace(@a,'&','&'),'<','<'),'|','</a><a>')+'</a>';
SELECT x.n.value('.','VARCHAR(1)') AS singleValue
FROM @xml.nodes('/a') AS x(n)
;