Split String Function

,

This In-line Table Valued Function takes a string of values along with a delimiter and breaks the string into a table using recursion. Support has been added for text qualified strings as well as space and tab delimited records.

Using a numbers table, we are able to take advantage of Recursion in SQL Server to find our delimiter and leverage the SUBSTRING function to break the string into its individual components without requiring the overhead of a loop.

Once the function has been created, splitting strings is quite simple.

DECLARE @String NVARCHAR(MAX)
    ,   @Delimiter NVARCHAR(3);
    
SELECT  @String = '"The","Quick","Brown","Fox","Jumped","Over","The","Lazy","Dog","0123456789","`~!@#$%^&*()_-+={}[]|\:;<,>.?/"'
    ,   @Delimiter = '","';
SELECT  s.Record
    ,   s.Value
    ,   @String AS [@String]
FROM    dbo.udf_SplitString(@String,@Delimiter) AS s;

This script will produce the following results.

Happy Scripting!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID(N'[dbo].[udf_SplitString]',N'IF') IS NOT NULL
    DROP FUNCTION [dbo].[udf_SplitString];
GO

CREATE FUNCTION [dbo].[udf_SplitString] 
(
    @String NVARCHAR(MAX)
,   @Separator NVARCHAR(3)
) 
RETURNS TABLE
AS
--====================================
-- Name:    udf_SplitString
-- Created: Chris Kutsch 03/31/2015
-- Usage:   Returns a table with individual 
--          records parsed from a string
--====================================
RETURN  
(
    SELECT  ROW_NUMBER() OVER (ORDER BY Number) AS [Record]
        ,   CASE 
                WHEN LEN( @Separator ) = 3 AND @Separator LIKE ('"%"') THEN     --Supports Text Qualified Values
                     CASE 
                        WHEN Number = 1 THEN --First Delimiter
                            SUBSTRING(
                                    SUBSTRING(@String, Number, CHARINDEX(@Separator, @String + @Separator, Number))
                                ,   2
                                ,   LEN(SUBSTRING(@String, Number, CHARINDEX(@Separator, @String + @Separator, Number)))-2
                            )
                        WHEN Number = LEN(@String)-CHARINDEX(@Separator,REVERSE(@String))+2 THEN --Last Delimiter
                            SUBSTRING(
                                    SUBSTRING(@String, Number, CHARINDEX(@Separator, @String + @Separator, Number) - Number)
                                ,   1
                                ,   CHARINDEX(@Separator,REVERSE(@String))-2
                            )
                        ELSE 
                            SUBSTRING(@String, Number, CHARINDEX(@Separator, @String + @Separator, Number) - Number)
                     END
                ELSE 
                    SUBSTRING(@String, Number, CHARINDEX(@Separator, @String + @Separator, Number) - Number)
            END AS [Value]
            
    FROM    adm.Numbers
    
            /*    If a numbers table doesn't exist, one can be generated on the fly, but costs are higher
            ( SELECT TOP 8000 ROW_NUMBER() OVER (ORDER BY s1.[OBJECT_ID]) AS [Number]   
              FROM sys.all_objects s1 CROSS APPLY
                   sys.all_objects s2
            ) tt 
            --Handles a maximum of 8000 characters. Change as needed, but performance degrades with large sets
            */         
    
    WHERE   Number <= LEN(@String) AND
            SUBSTRING(@Separator + @String, Number, IsNull(NULLIF(LEN(@Separator),0),1)) = @Separator
    
)
GO

Rate

4.13 (8)

Share

Share

Rate

4.13 (8)