Split string in SQL

/, SQL Server/Split string in SQL

Split is used to break a delimited string into multiple rows. The below user defined function udf_Split accepts 2 parameters.

  • Input the string that you want to split into multiple rows.
  • Delimiter the delimiter based on which the seperation will be performed.
CREATE FUNCTION [dbo].[udf_Split]
    (
     @Input VARCHAR(MAX)
    ,@Delimiter CHAR(1)
    )
RETURNS @RtnValue TABLE
    (
     Id INT IDENTITY(1,1)
    ,Data NVARCHAR(100)
    )
AS 
    BEGIN 
        DECLARE @Cnt INT
        SET @Cnt = 1

        WHILE ( CHARINDEX(@Delimiter,@Input) > 0 ) 
            BEGIN
                INSERT  INTO @RtnValue
                        ( data
                        )
                        SELECT  Data = LTRIM(RTRIM(SUBSTRING(@Input,1,
                                                             CHARINDEX(@Delimiter,
                                                              @Input) - 1)))

                SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter,@Input) + 1,
                                       LEN(@Input))
                SET @Cnt = @Cnt + 1
            END
	
        INSERT  INTO @RtnValue
                ( data )
                SELECT  Data = LTRIM(RTRIM(@Input))

        RETURN
    END

 

Usage:

SELECT * FROM dbo.udf_Split('This is the text I want to split based on a character', ' ')

It will return the following table
split string with sql

Update: Many thanks to Fred Zimmerman who pointed me to an old post written by Aaron Bertrand at sqlperformance for better ways on splitting a string with sql and clr, xml and common tables expression

Furthermore Aaron has several posts for splitting strings

2017-03-06T11:32:57+00:00 January 10th, 2017|SQL, SQL Server|0 Comments

Leave A Comment