SQL 2005拆分逗号分隔的列上分隔符

关于如何分割在分隔字符串我的谷歌搜索已经导致了当字符串被称为分割字符串一些有用的功能(如见下图):

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

这非常适用于已知的字符串,如:

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

不过,我想一列传递给一个函数,并将它与它自己的行我的其他数据一起联合在一起......比如提供的数据:

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

我想写类似:

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

并取回

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

这是可能的,或者以前有没有人这样做呢?

--------------解决方案-------------

是的,这可能与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

笔记:

  1. 你应该添加一个索引到您的拆分列的结果集,这样它会返回两列,IndexNumber和价值。
  2. 在网上有一个数字表实现普遍比这里您程序的版本速度更快。

例如:

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, @[email protected], Number)-Number
)
)))
from dbo.Numbers
where Number <= convert(int, len(@list))
and substring(@delimiter + @list, Number, 1) = @delimiter
)

厄兰Sommarskog对此有明确的页面,我认为:http://www.sommarskog.se/arrays-in-sql-2005.html

修复它正确的方式 - 让该列相关的表。 没有良好的有那种用逗号分隔的标量列。

+1防CSV意见,但如果你必须这样做,你可以使用CROSS APPLY和OUTER APPLY。

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 @[email protected]@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 @[email protected] +(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

用法:

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

你可以尝试这样的:

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

分类:SQL 时间:2015-03-15 人气:3
本文关键词: SQL,SQL Server 2005中
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.897 (s). 10 q(s)