SQL Tips #22 - A Function to Split a String

Older versions of SQL Server have no built-in Split function - this user-defined function splits a delimited string into a table of items.

The Query

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)

    SELECT @INDEX = 1
    WHILE @INDEX !=0
    BEGIN
        -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
        SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
        -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
        IF @INDEX !=0
        SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
    ELSE
        SELECT @SLICE = @STRING
        -- PUT THE ITEM INTO THE RESULTS SET
        INSERT INTO @Results(Items) VALUES(@SLICE)
        -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
        SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
        -- BREAK OUT IF WE ARE DONE
        IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags