How To split a string in SQL Server

SQL Function For Spliting a String in SQL SERVER

In SQL Server we do not have a built in function for splitting a string with any character. So we need to write the query for splitting a string in SQL. Here I am creating a function for splitting a string and getting the output data in a table.

CREATE FUNCTION [dbo].[fnSplitString] ( @MyArray VARCHAR(8000), @separator CHAR(1) )

RETURNS @RetTable TABLE

       (StrValue VARCHAR(256))

AS

BEGIN

       DECLARE @SeperatorString VARCHAR(10);

       SET @SeperatorString = '%' + @separator + '%'

       DECLARE @separator_position INT

       DECLARE @array_value VARCHAR(1000)

       SET @MyArray = @MyArray + @separator

       WHILE PATINDEX( @SeperatorString , @MyArray) <> 0

       BEGIN

              SELECT @separator_position =  PATINDEX(@SeperatorString , @MyArray)

              SELECT @array_value = LEFT(@MyArray, @separator_position - 1)

              INSERT @RetTable VALUES ( CAST(@array_value AS VARCHAR(256)) )

              SELECT @MyArray = STUFF(@MyArray, 1, @separator_position, '')

       END

       RETURN

END

This will create a new function. Now you can easily pass parameters to this function and get the data in a table format. Parameters are

@MyArray   - The full string which we need to split

@separator - The character by which we need to split

Here are some examples

Example 1 :

SELECT StrValue FROM dbo.[fnSplitString]('data1,data2,data3,data4',',')

The result will be

Here the separator is comma( , ).

Example 2 :

DECLARE @MyString VARCHAR(1000);

SET @MyString = 'data1;data2;data3;data4;1;2;3'

SELECT StrValue FROM dbo.[fnSplitString](@MyString,';')

 The result will be

Here the separator is semi-column(;)

Thanks for reading my post. Happy Coding. :)