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

    我们不止希望Sql 有split函数,我们希望得到的结果是有顺序的。结果是这样的

    if exists (select * from dbo.sysobjects where id = object_id(N'[splitwithID]') and xtype in (N'FN', N'IF', N'TF'))
    DROP FUNCTION [splitwithID]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    create function [dbo].[SplitWithID]
    (
        @InputStr varchar(4000) , @key varchar(10)
    )
    
    returns @ResultTable table
    (
       val varchar(100),id int
    )
    
    as
    begin
    
    declare @str varchar(4000)
    declare @substr varchar(250)
    declare @iLen int
    declare @iStart int, @k int;
    set @str=rtrim(ltrim(@InputStr))
    set @k=0;
    
    set @iStart=charindex(@key, @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 ) );
    set @k=@k+1;
    insert @ResultTable select @substr,@k;
    
    while len( @str )>0
    begin
        ---------------- Loop Begin ---------------
    
        set @iStart=charindex( @key , @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 ) )
        set @k=@k+1;
        insert @ResultTable select @substr,@k
    
        ---------------- Loop End ----------------
    end
    
    return
    end
    
    
    go

     

     

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