SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software

Development Forums for Programmer, Administrators and Users

Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

T-SQL Split User Defined Function

Last post 06-04-2010, 1:11 PM by praveenbattula. 12 replies.
Sort Posts:
  •  01-09-2007, 8:56 AM 489

    T-SQL Split User Defined Function

    Here is a simple t-sql Split User Defined Function (UDF) which you can use for splitting an input string by an other input seperator string.

    This sql Split UDF returns a record set of splitted values.

     

    CREATE FUNCTION [dbo].[SPLIT] (

    @str_in VARCHAR(8000),

    @separator VARCHAR(4) )

    RETURNS @strtable TABLE (strval VARCHAR(8000))

    AS

    BEGIN

    DECLARE

    @Occurrences INT,

    @Counter INT,

    @tmpStr VARCHAR(8000)

    SET @Counter = 0

    IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

    SET @str_in = @str_in + @separator

    SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

    SET @tmpStr = @str_in

    WHILE @Counter <= @Occurrences

    BEGIN

    SET @Counter = @Counter + 1

    INSERT INTO @strtable

    VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

    SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

    IF DATALENGTH(@tmpStr) = 0

    BREAK

    END

    RETURN

    END

     

  •  01-08-2008, 11:20 AM 771 in reply to 489

    Re: T-SQL Split User Defined Function

    Hi all,

    I have uploaded the t-sql user-defined split function to Files section page titled Sample T-SQL SPLIT function code where you can easily download the sql code of the tsql function.

    Eralper
  •  02-21-2008, 12:39 AM 835 in reply to 489

    Re: T-SQL Split User Defined Function

    First of all, thank you very much for sharing this useful function. I only taking the portion of splitting string only...

    I have some doubts and plan to put here for discussion... hope you don't mind...

    Doubt 1:
    Do you plan to let this function support separator (or some ppl may called delimiter) more than 1 byte? (eg: using double pipes ( || ) ... etc.)

    eralper:

    IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

    Base on my understanding, this IF statement is checking whether last element is empty or not by taking the last char in @str_in and compare with @separator. If @separator is the last char means last element is missing right? (eg: "
    string1,string2,string3,"). The "1" there is actually limited the @separator to 1 byte, isn't it?

    eralper:

    SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

    But, here you devide datalength(@separator), which means if separator is more than 1 byte, here is still able to provide accurate answer.

    My suggestion on the if statement:
    IF SUBSTRING(@str_in,datalength(@str_in) - datalength(@separator) + 1, datalength(@separator)) <> @separator


    Doubt 2:
    Actually not a doubt la... just suggestion only...
    eralper:

    SET
    @str_in = @str_in + @separator

    SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)


    I find it a bit complicated, and below is my own formula, so far tested is works...
    SET @str_in = @str_in + @separator    --Remained the same

    SET @Occurrences =(datalength(@str_in) - datalength(replace(@str_in,@separator,''))) / datalength(@separator)


  •  02-21-2008, 3:43 AM 837 in reply to 835

    Re: T-SQL Split User Defined Function

    Hi richochb,

    Thanks for your reviews on the code!

    Let me check the points you have issued.

    I believe, after your comments the split function will be more reliable.

  •  02-22-2008, 3:34 PM 842 in reply to 835

    Re: T-SQL Split User Defined Function

    Hello richochb,

    I have taken into care the points you have noted. And I agree to the points you have issued in deed.

    First of all, if seperator is more than one character than it is creating a problem. Your doubt on that point is truely right.

    After a re-work on the function, I believe I had a better version of the split function.

    I'm copying down here the codes also you can download it from T-SQL Split function download link.

    If you can find time and work on this new split function, I'll be glad to get feedback from you.

    Thanks and best regards,
    Eralper

  •  02-25-2008, 5:44 AM 846 in reply to 842

    Re: T-SQL Split User Defined Function

    Hello Eralper,

    Thank you very much for taking some time to re-work on the function. This new function is way more simple yet powerful...


    Basically, I have 2 comments:

    1) It seems like cannot handle string which have space in between (eg: "aa, bb, cc"). Actually this is left out when my last post, because I just face it today. My solution is clearing all the spacing.
    However, there maybe have certain case where spacing is needed, so it's depends on situation. Below is my suggestion of coding: (green is remarked from your coding while blue is my new added in...)


    SELECT @tmpStr = /* @str_in */ replace(@str_in, space(1) , ''), @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))


    2) Personal comment which do not relate to functionlity: Two times of insertion may cause it look massy if insertion coding is long (eg: table have a lot of fields to insert). I'll prefer to assign it into a variable instead of directly insert into table.

    Below is edited version from your coding. (haiz... i duno how to upload my coding to kodyaz web site... )



    GO

    CREATE FUNCTION [dbo].[SPLIT] (
        @str_in VARCHAR(8000),
        @separator VARCHAR(4)
    )
    RETURNS @strtable TABLE (strval VARCHAR(8000))

    AS

    BEGIN

    DECLARE @tmpStr VARCHAR(8000), @charind INT, @SeperatorLength INT, @finalStr varchar(100)


    --SELECT @tmpStr = @str_in, @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))
    SELECT @tmpStr = replace(@str_in, space(1) , ''), @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))

    WHILE LEN(@tmpStr) > 0
       BEGIN

        SET @charind = CHARINDEX(@separator, @tmpStr)

        IF @charind > 0
           BEGIN
            IF @charind > 1

                --INSERT INTO @strtable SELECT SUBSTRING( @tmpStr, 1, CHARINDEX(@separator,@tmpStr)-1 )
                select @finalStr = SUBSTRING( @tmpStr, 1, CHARINDEX(@separator,@tmpStr)-1 )
                SELECT @tmpStr = SUBSTRING(@tmpStr, @charind + @SeperatorLength, LEN(REPLACE(@tmpStr, SPACE(1), 'X')) - (@charind + @SeperatorLength - 1) )
           END
        ELSE
           BEGIN

            --INSERT INTO @strtable SELECT @tmpStr
            select @finalStr = @tmpStr
            SELECT @tmpStr = ''
           END


        insert into @strtable select @finalStr

       END

    RETURN

    END

    GO




    Thanks & Best Regards,
    richochb

  •  02-25-2008, 10:15 AM 848 in reply to 846

    Re: T-SQL Split User Defined Function

    Hi richochb,

    Thanks again for your concern on split udf function.

    The spaces are actually a problem for the split function if used as a seperator. Spaces were problem for the previous version of this function. I thought that any character in the string should be kept so I did not trim the resultant list.

    I think, the script can be modified or a parameter can be added which specifies the trailing spaces to be trimmed or not. And those values can be inserted to the returned table according to that parameter value.

    One more point that can be customized is that, we can use the DATALENGTH function to find the length of the pieces especially for the seperator parameter. Instead I used the following way, replacing spaces with X character and then taking the length which can be modified

    @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))

    But I don't think it is a good way to replace all the spaces in the input string variable, instead of removing all of the spaces, as I said trailing spaces can be removed.


    For the second point you have added, you are right that instead of directly inserting into the return table, splitted pieces can be kept in a variable and then inserted
  •  02-25-2008, 7:24 PM 850 in reply to 848

    Re: T-SQL Split User Defined Function

    Hi Eralper,

    Thanks for all your precious comments.

    The spacing in my case have to eliminated, but not every case need to do so.

    My program is using ASP checkbox (form) passing value into stored procedure. The string from checkbox will come out like "AA, BB, CC" by default. So, if not eliminated the spacing in between, it will end up getting something like " BB" in the table. However, if the case is something like "sport car,air craft,ship,spaceship  " then it just need to trim the trailing spaces.

    Anyway, your program is really useful and it help me a lot... Thank you very much.


    Best Regards,
    richochb
  •  02-26-2008, 12:09 AM 851 in reply to 850

    Re: T-SQL Split User Defined Function

    Hi richochb, I thank alot to you for your comments also. You make me notice that the split function is not functioning as well as I guessed. Thanks again for your contribute Eralper
  •  11-18-2008, 12:09 AM 3543 in reply to 489

    Re: T-SQL Split User Defined Function

    Hi
    I have got a problem here but cannot solve it. i am trying to call a UDF but it gives me the following error
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fncs_split", or the name is ambiguous.

    i call using simple statement like

    select dbo.fncs_split ('564465,545445,656545', ',') as t

    and modified your code a little bit

    here is the code for your UDF that i changed.

    CREATE FUNCTION dbo.fncs_split (
    @str_in NVARCHAR(500),
    @separator VARCHAR(4)
    )
    RETURNS @strtable TABLE (strval NVARCHAR(500))

    AS

    BEGIN

    DECLARE @tmpStr NVARCHAR(500), @charind INT, @SeperatorLength INT

    SELECT @tmpStr = @str_in, @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))

    WHILE LEN(@tmpStr) > 0
    BEGIN

    SET @charind = CHARINDEX(@separator, @tmpStr)

    IF @charind > 0
    BEGIN
    IF @charind > 1
    INSERT INTO @strtable SELECT SUBSTRING( @tmpStr, 1, CHARINDEX(@separator,@tmpStr)-1 )

    SELECT @tmpStr = SUBSTRING(@tmpStr, @charind + @SeperatorLength, LEN(REPLACE(@tmpStr, SPACE(1), 'X')) - (@charind + @SeperatorLength - 1) )
    END
    ELSE
    BEGIN
    INSERT INTO @strtable SELECT @tmpStr
    SELECT @tmpStr = ''
    END

    END

    RETURN

    END

    Please suggest the possible error and solution. I really need this function
  •  11-18-2008, 12:12 AM 3544 in reply to 3543

    Re: T-SQL Split User Defined Function

    Here it is in Formatting

    ALTER FUNCTION dbo.fncs_split (@str_in NVARCHAR(500), @separator VARCHAR(4))

    RETURNS @strtable TABLE (strval NVARCHAR(500))

    AS

    BEGIN

       DECLARE @tmpStr NVARCHAR(500), @charind INT, @SeperatorLength INT

       SELECT @tmpStr = @str_in, @SeperatorLength = LEN(REPLACE(@separator, SPACE(1), 'X'))

       WHILE LEN(@tmpStr) > 0

        BEGIN

           SET @charind = CHARINDEX(@separator, @tmpStr)

              IF @charind > 0

              BEGIN

                IF @charind > 1

                   INSERT INTO @strtable SELECT SUBSTRING( @tmpStr, 1, CHARINDEX(@separator,@tmpStr)-1 )

                   SELECT @tmpStr = SUBSTRING(@tmpStr, @charind + @SeperatorLength, LEN(REPLACE(@tmpStr, SPACE(1), 'X')) - (@charind + @SeperatorLength - 1) )

             END

             ELSE

             BEGIN

                   INSERT INTO @strtable SELECT @tmpStr

                   SELECT @tmpStr = ''

             END

       END

    RETURN

    END

    GO

  •  11-19-2008, 5:58 AM 3545 in reply to 3544

    Re: T-SQL Split User Defined Function

    Hi aamirwazir,


    Since the user defined function is declared as table-valued function which means actually that it returns a table, you should use the function as below :

    select t.* from dbo.fncs_split('564465,545445,656545', ',') as t

    You can use the table-valued functions just as tables in a SELECT t-sql query.

    Thanks for feedback, I hope you find it helpful.
    Eralper
  •  06-04-2010, 1:11 PM 21098 in reply to 3545

    Re: T-SQL Split User Defined Function

    I think, this is what exactly you are looking for:
    T-SQL Split function and returns data as table
View as RSS news feed in XML
Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems