SQL Server 2008 中的逗号分割函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20512855/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split function by comma in SQL Server 2008
提问by user9969
I know that this question has been asked many times but could not find what I needed.
我知道这个问题已被问过很多次，但找不到我需要的。
I have this column "Order" which contains data in the following format. 'xxx,yyy,zzzz'
Now when I do my selectstatement I need to populate 3 columns by splitting this one
我有这个“订单”列，其中包含以下格式的数据。'xxx,yyy,zzzz'
现在，当我做我的select陈述时，我需要通过拆分这一列来填充 3 列
E.G.
例如
Select Name,
Surname,
FirstCommaColumn=xx.UpToFirstColumn
SecondCommaColumn=xx.FromFirstCommaToLastComma,
ThirdColumnFromSecondCommaOnwards=FromSecondCommaToEnd
from myTable
--thought of doing something like
CROSS APPLY (SELECT TOP 1 * FROM dbo.SplitFunctionIDontHave(order,',')) AS xx
There are some rows which have no commas so I must return blank. I don't mind if I do in a function or within the query itself just not sure how to do this.
有些行没有逗号，所以我必须返回空白。我不介意我是在函数中还是在查询本身中这样做，只是不知道如何做到这一点。
How can I do this using SQL Server 2008? This select is part of a view if makes a difference
如何使用 SQL Server 2008 执行此操作？ 如果有所作为，则此选择是视图的一部分
回答by Jade
I've change the function name so it won't overlapped in what the Split()function really does.
我已经更改了函数名称，因此它不会与Split()函数的实际功能重叠。
Here is the code:
这是代码：
CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return null
declare @ColCnt int
set @ColCnt = 1
while (@idx != 0)
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0 begin
if (@ColCnt = @Column) return left(@String,@idx - 1)
set @ColCnt = @ColCnt + 1
end
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return @String
end
And here is the usage:
这是用法：
select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)
回答by Omkar Naik
Declare @str as Varchar(100) = '10|20|30|40|500|55'
Declare @delimiter As Varchar(1)='|'
Declare @Temp as Table ( item varchar(100))
Declare @i as int=0
Declare @j as int=0
Set @j = (Len(@str) - len(REPLACE(@str,@delimiter,'')))
While @i < = @j
Begin
if @i < @j
Begin
Insert into @Temp
Values(SUBSTRING(@str,1,Charindex(@delimiter,@str,1)-1))
set @str = right(@str,(len(@str)- Charindex(@Delominator,@str,1)))
End
Else
Begin
Insert into @Temp Values(@str)
End
Set @i = @i + 1
End
Select * from @Temp
回答by Jade
There is no Split()function in SQL Server, but you can create user defined function.
Split()SQL Server 中没有函数，但您可以创建用户定义的函数。
see this answer How to split a comma-separated value to columns
看到这个答案如何将逗号分隔的值拆分为列
回答by Indranil.Bharambe
create the below function and use as below
创建以下功能并使用如下
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
SELECT TOP 1 * FROM dbo.Split(order,',')
回答by Jayvee
it seems like a good case to play with parsename.
使用 parsename 似乎是一个很好的例子。
Edited to use @Order as example:
编辑以使用@Order 作为示例：
DECLARE @Order VARCHAR(MAX) = 'xxx,yyy,zzzz'
SELECT FirstCommaColumn=PARSENAME(REPLACE(@Order,',','.'),3),
SecondCommaColumn=PARSENAME(REPLACE(@Order,',','.'),2),
ThirdColumnFromSecondCommaOnwards=PARSENAME(REPLACE(@Order,',','.'),1)
回答by Johnny
CREATE FUNCTION [dbo].[splitStr] (
@str NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
RowID smallint IDENTITY(1,1),
t1 NVARCHAR(MAX) ,
t2 NVARCHAR(MAX) ,
t3 NVARCHAR(MAX) ,
t4 NVARCHAR(MAX) ,
t5 NVARCHAR(MAX) ,
t6 NVARCHAR(MAX) ,
t7 NVARCHAR(MAX) ,
t8 NVARCHAR(MAX) ,
t9 NVARCHAR(MAX) ,
t10 NVARCHAR(MAX)
)
begin
declare @st int, @en int, @xx int
declare @cntr int
set @cntr = 0
set @st = 1
select @en = CHARINDEX(@delimiter, @str, @st)
if @en = 0
set @en = LEN(@str)
while @en <= LEN(@str) and @cntr < 11 begin
set @cntr = @cntr + 1
set @xx = @en - @st
if @cntr = 1
insert into @output(t1) values(SUBSTRING(@str, @st, @xx))
if @cntr = 2
update @output set t2 = SUBSTRING(@str, @st, @xx)
if @cntr = 3
update @output set t3 = SUBSTRING(@str, @st, @xx)
if @cntr = 4
update @output set t4 = SUBSTRING(@str, @st, @xx)
if @cntr = 5
update @output set t5 = SUBSTRING(@str, @st, @xx)
if @cntr = 6
update @output set t6 = SUBSTRING(@str, @st, @xx)
if @cntr = 7
update @output set t7 = SUBSTRING(@str, @st, @xx)
if @cntr = 8
update @output set t8 = SUBSTRING(@str, @st, @xx)
if @cntr = 9
update @output set t9 = SUBSTRING(@str, @st, @xx)
if @cntr = 10
update @output set t10 = SUBSTRING(@str, @st, @xx)
set @st = @en + 1
if @st > len(@str)
begin
set @en = @en + 100
end
else
begin
select @en = CHARINDEX(@delimiter,@str, @st)
if @en = 0
begin
set @en = LEN(@str)
set @xx = @en - @st
end
end
end
return
end
/*
/*
This will allow you to split up to 10 fields out by delimiter. You can add more to the list if your needs exceed 10.
这将允许您按分隔符拆分最多 10 个字段。如果您的需求超过 10，您可以将更多添加到列表中。
usage
用法
select * from TableName a
cross apply splitStr(a.FiledName, ',')
*/
*/
