Split Function In Sqlserver 2005

1) create below function into database.

-- pass the string 1,2,3
-- delimeter here is ,
Create FUNCTION [dbo].[SplitValue](@Text varchar(Max), @Delimeter varchar(2) = ' ')
RETURNS @ReturnValue TABLE (id int , value varchar(Max))
AS
BEGIN
DECLARE @id int,
    @value varchar(Max),
    @cont bit,
    @strik int,
    @Delimlength int

IF @Delimeter = 'Space'
BEGIN
    SET @Delimeter = ' '
END
--initialize id with 0
SET @id = 0
SET @Text = LTrim(RTrim(@Text))
SET @Delimlength = DATALENGTH(@Delimeter)
SET @cont = 1

IF NOT ((@Delimlength = 0) or (@Delimeter = 'Empty'))
    BEGIN
        WHILE @cont = 1
        BEGIN
        --If you can find the delimiter in the text, retrieve the first element and
        --insert it with its index into the return table.
        IF CHARINDEX(@Delimeter, @Text)>0
            BEGIN
                SET @value = SUBSTRING(@Text,1, CHARINDEX(@Delimeter,@Text)-1)
                BEGIN
                    INSERT @ReturnValue (id, value)
                    VALUES (@id, @value)
                END
                --Increment the index and loop.
                SET @strik = DATALENGTH(@value) + @Delimlength
                SET @id = @id + 1
                SET @Text = LTrim(Right(@Text,DATALENGTH(@Text) - @strik))
            END
        ELSE
        BEGIN
        --If you can’t find the delimiter in the text, @Text is the last value in
        --@ReturnValue.
            SET @value = @Text
            BEGIN
                INSERT @ReturnValue (id, value)
                VALUES (@id, @value)
            END
        --Exit the WHILE loop.
            SET @cont = 0
        END
    END
END
ELSE
BEGIN
    WHILE @cont=1
    BEGIN
    --If the delimiter is an empty string, check for remaining text
    --instead of a delimiter. Insert the first character into the
    --retArray table. Trim the character from the front of the string.
    --Increment the index and loop.
        IF DATALENGTH(@Text)>1
            BEGIN
                SET @value = SUBSTRING(@Text,1,1)
                BEGIN
                INSERT @ReturnValue (id, value)
                VALUES (@id, @value)
                END
                SET @id = @id+1
                SET @Text = SUBSTRING(@Text,2,DATALENGTH(@Text)-1)
            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @ReturnValue (id, value)
            VALUES (@id, @Text)
            SET @cont = 0
            END
    END
END
RETURN
END
2) run the following query
select * from dbo.SplitValue('1,2,3',',')
* Here i used reference from othersites.
Thnx.

Advertisements

8 comments

  1. pls use proper font size in your blog.

  2. Hi Nirav,
    Thanks For your valuable comment.

    Regards,
    Amit S Patriwala.

  3. Thanks…. Your post is good.
    But i have written with lesser lines.
    Split Function in Sql Server

  4. Amit Kohli · · Reply

    Thankx buddy for such a nice function….

  5. Your post is good.
    so thanks very much

  6. Hi Patriwala,
    i have a table field ‘Factor’ with different values on each row.
    If i use a parameter @Factor in SSRS and used this function in my query, its not working. I don’t know if this function is designed for my scinario. You help will be greatly appreciated.

    Thanks
    Sam

    1. hi Sam,
      I am not understand the actual scenario what to do? please provide me the more details.

      Thanks & Regards,
      Amit S Patriwala.

  7. hey dude i want the function of multiple colomn value in to the multiple row

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s