SQL Server 2008 中的逗号分割函数 [英] Split function by comma in SQL Server 2008

问题描述

我知道这个问题已经被问过很多次了，但找不到我需要的.

I know that this question has been asked many times but could not find what I needed.

我有一列订单"，其中包含以下格式的数据.'xxx,yyy,zzzz'
现在，当我执行 select 语句时，我需要通过拆分这一列来填充 3 列

I have this column "Order" which contains data in the following format. 'xxx,yyy,zzzz'
Now when I do my select statement I need to populate 3 columns by splitting this one

例如

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.

如何使用 SQL Server 2008 执行此操作?如果有所不同，此选择是视图的一部分

How can I do this using SQL Server 2008? This select is part of a view if makes a difference

推荐答案

我已经更改了函数名称，因此它不会与 Split() 函数的实际功能重叠.

I've change the function name so it won't overlapped in what the Split() function really does.

代码如下:

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

这是用法:

select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)

这篇关于SQL Server 2008 中的逗号分割函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！