Kodyaz Development Resources

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

 

---

Eralper
Kodyaz.com

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

---

Eralper
Kodyaz.com

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)

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.

---

Eralper
Kodyaz.com

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

---

Eralper
Kodyaz.com

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

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

---

Eralper
Kodyaz.com

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

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

---

Eralper
Kodyaz.com

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

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

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

---

Eralper
Kodyaz.com

I think, this is what exactly you are looking for:
T-SQL Split function and returns data as table