User defined function to split string by delimiter

by Amol 10. September 2009 11:53

In SQL Server, there is no inbuilt function to split the string by delimiter. If there is need to split string, user defined function has to be created for this task.

Main purpose of split function is to split given string by given delimiter. Here is the user defined SQL Server split function.

CREATE FUNCTION [dbo].[Split]
(   
    @InputString VARCHAR (MAX), 
    @Delimiter VARCHAR (10)
)
RETURNS @TempTable TABLE ([Id] INT IDENTITY (1, 1) NOT NULL,
                          [Values] VARCHAR (8000) NULL)
AS
BEGIN
    DECLARE  @Length INT
            ,@Index INT
            ,@LastIndex INT
            ,@Counter INT
    SET @InputString = @InputString + @Delimiter
    SET @Length = len(@InputString)
    SET @Index = 1
    SET @Counter = 1
    WHILE (@Counter < @Length)
    BEGIN
        IF charindex(@Delimiter, @InputString, @Index) > 0 
        BEGIN
            SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
            INSERT INTO @TempTable ([Values]) 
            SELECT substring(@InputString, @Index, @LastIndex)
            SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
        END
        SET @Counter = @Counter + 1
    END
RETURN
END

 

Tags: ,

SQL Server Scripts

Category

Recent Posts

Tag cloud