Create a Split Function Using T-SQL

SQL Server has a lot of handy string manipulation functions built-in but I often find I’m in need of the Split function .Net developers have readily available.  It’s nice to be able to easily break a delimited string into an array of string tokens broken apart by a specific delimiting character.

Here’s a table-valued UDF that will do the trick:

IF OBJECT_ID('dbo.Split') IS NOT NULL
    DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split (
      @string varchar(MAX)
    , @separator char(1)
    )
RETURNS @Results TABLE (
      string varchar(255) NOT NULL
    , startIndex int NOT NULL
    )
AS
/*
Object:   dbo.Split
Description:  Splits the passed string based on the separator provided
 
Usage:    @string = the string that needs to split into peices
    @separator = the character to used to determine where to split the string
 
Returns:   Table
 
$Workfile: Split.sql $
$Author: David Masciangelo (http://sql-ution.com)$
$Revision: 1.0 $
Created: 05/05/2010 09:42
$Modtime: 05/05/2010 09:42 $
Modification History:
 
*/
 
BEGIN
    DECLARE @position int
      , @index int
-- UnREM for testing
--  , @string varchar(MAX)
--  , @separator char(1)
--SET @string = 'string1 string2,string3 string4'
--SET @separator = ' '
--DECLARE @Results TABLE (
--      string varchar(255) NOT NULL
--    , startIndex int NOT NULL
--    )
    SET @position = 1
    IF PATINDEX('%[' + @separator + ']%', @string) <> 0
        BEGIN
 
            WHILE PATINDEX('%[' + @separator + ']%', @string) <> 0
                BEGIN
 
                    SET @index = PATINDEX('%[' + @separator + ']%', @string)
 
                    INSERT  INTO @Results
                            SELECT  LEFT(@string, @index - 1)
                                  , @position
 
                    SET @string = RIGHT(@string, (LEN(@string) - @index))
                    SET @position = @position + @index
                END
 
        END
 
    INSERT  INTO @Results
            SELECT  @string
                  , @position
                 
    RETURN
 
END
GO
GRANT SELECT ON dbo.Split TO public
GO
SET NOCOUNT OFF
GO

Leave a Reply

Your email address will not be published. Required fields are marked *