SQL字符串拆分函数-优快云博客

本文介绍了一个SQL Server中用于拆分字符串的自定义函数SplitString。该函数接收四个参数：待拆分的字符串、分隔符、是否移除空项及指定返回的位置。通过示例展示了如何使用此函数来提取日期格式中的年、月、日。

create function [dbo].[SplitString]

(
    @Input nvarchar(max),
    @Separator nvarchar(max)=',',
    @RemoveEmptyEntries bit=1 ,
    @position int
)
returns @TABLE table
(
    [Id] int identity(1,1),
    [Value] nvarchar(max)
)
as
begin
    declare @Index int, @Entry nvarchar(max)
    set @Index = charindex(@Separator,@Input)
    set @Input = @Input + @Separator

    declare @count int
    set @count = 1
    while (@Index>0)
    begin
        set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))

        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
              if @count = @position
                begin
                   insert into @TABLE([Value]) Values(@Entry)
                end
            end

        set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
        set @Index = charindex(@Separator, @Input)
        set @count = @count + 1;
    end

    return
end

------------------------------

     select [Value] from [dbo].[SplitString2]('2012-12-25', '-', 1,1)
     select [Value] from [dbo].[SplitString2]('2012-12-25', '-', 1,2)
     select [Value] from [dbo].[SplitString2]('2012-12-25', '-', 1,3)