My google searches on how to split a string on a delimiter have resulted in 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 like:
这适用于已知字符串,如:
SELECT TOP 10 * FROM dbo.Split('This,Is,My,List',',')
However, I would like to pass a column to a function, and have it unioned together with my other data in it's 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 get 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?
这可能,或者之前有人这样做过吗?
5 个解决方案
#1
Yes, it's possible with CROSS APPLY (SQL 2005+):
是的,可以使用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.
您应该为拆分列的结果集添加索引,以便它返回两列,IndexNumber和Value。
-
In-line implementations with a numbers table are generally faster than your procedural version here.
使用数字表的内联实现通常比此处的过程版本更快。
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 definitive page on this, I think: http://www.sommarskog.se/arrays-in-sql-2005.html
我认为Erland Sommarskog有关于此的最终页面:http://www.sommarskog.se/arrays-in-sql-2005.html
#2
Fix it the right way--make that column a related table. No good ever comes of comma-separated scalar columns.
以正确的方式修复它 - 使该列成为一个相关的表。以逗号分隔的标量列没有好处。
#3
+1 to the anti-CSV comments, but if you must do this you would use CROSS APPLY or OUTER APPLY.
+1反垃圾评论,但如果你必须这样做,你将使用CROSS APPLY或OUTER APPLY。
#4
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
Usage:
exec usp_split '1212,21213,1,3,133,1313131,1,231313,5'
#5
You could try something like:
你可以尝试类似的东西:
SELECT s.Items AS SplitValue, ValueColumn1, ValueColumn2
FROM MyTable, Split(CommaColumn,',') AS s
#1
Yes, it's possible with CROSS APPLY (SQL 2005+):
是的,可以使用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.
您应该为拆分列的结果集添加索引,以便它返回两列,IndexNumber和Value。
-
In-line implementations with a numbers table are generally faster than your procedural version here.
使用数字表的内联实现通常比此处的过程版本更快。
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 definitive page on this, I think: http://www.sommarskog.se/arrays-in-sql-2005.html
我认为Erland Sommarskog有关于此的最终页面:http://www.sommarskog.se/arrays-in-sql-2005.html
#2
Fix it the right way--make that column a related table. No good ever comes of comma-separated scalar columns.
以正确的方式修复它 - 使该列成为一个相关的表。以逗号分隔的标量列没有好处。
#3
+1 to the anti-CSV comments, but if you must do this you would use CROSS APPLY or OUTER APPLY.
+1反垃圾评论,但如果你必须这样做,你将使用CROSS APPLY或OUTER APPLY。
#4
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
Usage:
exec usp_split '1212,21213,1,3,133,1313131,1,231313,5'
#5
You could try something like:
你可以尝试类似的东西:
SELECT s.Items AS SplitValue, ValueColumn1, ValueColumn2
FROM MyTable, Split(CommaColumn,',') AS s