首页新闻招聘找找看知识库
  • 浏览:16 2019-01-04 17:57 来自 程序新青年

    c#中有split函数用来分隔字符串,我们也希望SQL Server可以提供一样的功能,可以方便分割字符。

    /***You DON'T install Version Control in the DB****/
    -------------------------- Functions --------------------------
    if exists (select * from dbo.sysobjects where id = object_id(N'[XSplit]') and xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [XSplit]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Below function is borrowed from Split(@InputStr)
    create function [dbo].[XSplit]
    (
        @InputStr varchar(max),
        @Separator char(1)
    )
    
    returns @ResultTable table
    (
       val varchar(100)
    )
    as
    begin
        declare @str varchar(max)
        declare @substr varchar(250)
        declare @iLen int
        declare @iStart int
        set @str=RTrim(Ltrim(@InputStr))
    
        set @iStart=CHARINDEX( @Separator , @str )
        set @iLen=Len( @str )
        if @iStart>0
        begin
            set @substr=substring( @str , 1 , @iStart-1 )
            set @str=substring( @str , @iStart+1 , @iLen-@iStart )
        end
        else
        begin
            set @substr=@str
            set @str=''
        end
    
        set @substr=RTRIM( LTRIM( @substr ) )
        insert @ResultTable select id=@substr
    
        while Len( @str )>0
        begin
            ---------------- Loop begin ---------------
            set @iStart=CHARINDEX( @Separator , @str )
            set @iLen=Len( @str )
            if @iStart>0
            begin
                set @substr=substring( @str , 1 , @iStart-1 )
                set @str=substring( @str , @iStart+1 , @iLen-@iStart )
            end
            else
            begin
                set @substr=@str
                set @str=''
            end
    
            set @substr=RTRIM( LTRIM( @substr ) )
            insert @ResultTable select id=@substr
    
            ---------------- Loop end ----------------
        end
        return
    end
    
    GO

     

登录后才能评论,请先登录注册