Geek Answers Handbook

SQL 2005 Separator Column with Separator on Separator

My google searches for how to split a string on a separator led to some useful functions for splitting strings when the string is known (i.e. see below):

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[Split] (@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

This works well for a known string, for example:

 SELECT TOP 10 * FROM dbo.Split('This,Is,My,List',',')

However, I would like to pass the column to the function and combine it together with my other data in my own row ... for example, given the data:

 CommaColumn ValueColumn1 ValueColumn2 ----------- ------------ ------------- ABC,123 1 2 XYZ, 789 2 3

I would like to write something like:

 SELECT Split(CommaColumn,',') As SplitValue, ValueColumn1, ValueColumn2 FROM MyTable

And come back

 SplitValue ValueColumn1 ValueColumn2 ---------- ------------ ------------ ABC 1 2 123 1 2 XYZ 2 3 789 2 3

Is this possible, or has anyone done this before?

+6
sql sql-server-2005
source share
5 answers

Yes, this is possible with CROSS APPLY (SQL 2005+):

 with testdata (CommaColumn, ValueColumn1, ValueColumn2) as ( select 'ABC,123', 1, 2 union all select 'XYZ, 789', 2, 3 ) select b.items as SplitValue , a.ValueColumn1 , a.ValueColumn2 from testdata a cross apply dbo.Split(a.CommaColumn,',') b

Notes:

  • You should add an index to the result set of your split column so that it returns two columns: IndexNumber and Value.

  • Embedded versions with a number table are usually faster than your procedural version.

eg:

 create function [dbo].[Split] (@list nvarchar(max), @delimiter nchar(1) = N',') returns table as return ( select Number = row_number() over (order by Number) , [Value] = ltrim(rtrim(convert(nvarchar(4000), substring(@list, Number , charindex(@delimiter, @ list+@delimiter , Number)-Number ) ))) from dbo.Numbers where Number <= convert(int, len(@list)) and substring(@delimiter + @list, Number, 1) = @delimiter )

Erland Sommarskog has the final page, I think: http://www.sommarskog.se/arrays-in-sql-2005.html

+13
source share

Correct it correctly - make the column a linked table. Nothing good comes from scalar columns separated by commas.

+10
source share

+1 comments against CSV, but if you do, you'll use CROSS APPLY or OUTER APPLY.

+1
source share
 alter procedure [dbo].[usp_split](@strings varchar(max)) as begin Declare @index int set @index=1 declare @length int set @length=len(@strings) declare @str varchar(max) declare @diff int declare @Tags table(id varchar(30)) while(@index<@length) begin if(@index='1') begin set @str=(SELECT substring(@strings, @index, (charindex(',',(substring(@strings, @index,(@length)))))-1)) insert into @Tags values(@str) set @index=(charindex(',',(substring(@strings, @index,(@length))))) end else begin set @ diff=@length- @index if(@diff !=0) begin set @str=(select substring(@strings, @index, (charindex(',',(substring(@strings,@index,@diff))))-1)) if(@str is not null and @str!='') begin insert into @Tags VALUES(@str) end set @ index=@index +(charindex(',',(substring(@strings, @index,@diff)))) end end end set @str=(select right(@strings,(charindex(',',(substring(reverse(@strings),1,(@length)))))-1)) insert into @Tags VALUES(@str) select id from @Tags end

Using:

 exec usp_split '1212,21213,1,3,133,1313131,1,231313,5'
+1
source share

You can try something like:

 SELECT s.Items AS SplitValue, ValueColumn1, ValueColumn2 FROM MyTable, Split(CommaColumn,',') AS s
0
source share

More articles:


All Articles