User-Defined Function To Split String In SQL Server

The split string is one required functionality implementation while fledging your application. The user-defined function is useful if you are using a SQL Server machine older than 2016. The main use of the function is to convert a single string of row data into split table values in the form of rows. The string can be delimited either by a comma or any special character in accordance with your data.

Further, if you are using SQL Server version2016 and above, please use the STRING_SPLIT function for better performance. Additionally, if you face any difficulties in using the STRING_SPLIT function that possess error as – 'Invalid Object Name STRING_SPLIT', please refer the details on the link for the fix.

Mostly, we have strings separated by commas in our majority usages. However, we will see the examples of user-defined functions accepting just comma delimiters as well as the ones accepting special characters between the string values.

Split String Function (Accepting the only comma delimiters)

Step 1: Create a function as below:

CREATE FUNCTION Fn_split_string_OnlyComma
(
@in_string VARCHAR(MAX),
@delimiter VARCHAR(1)
)
RETURNS @list TABLE(tab_splitstring VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tab_splitstring)
SELECT left(@in_string, charindex(@delimiter, @in_string+',') -1) as tab_splitstring
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), '')
end
RETURN
END

Step 2: Select from the function – Fn_split_string_OnlyComma

select * from Fn_split_string_OnlyComma ('mango,orange,pineapple',',')

Note: If you are using any special character apart from comma character is going to error out.

Split String Function (Accepting any special character delimiters)

Step 1: Create a function as below:

CREATE FUNCTION [dbo].[fn_Split_String_AnySCharacter]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END

Step 2: Select from the function - fn_Split_String_AnySCharacter

Examples of splitting the string with ‘#’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango#orange#pineapple','#')

User Defined Function Split String SQL Server 2

Examples of splitting the string with ‘?’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango?orange?pineapple','?')

User Defined Function Split String SQL Server 4

Finally, an example of splitting the string with ‘,’ special character:

select * from [fn_Split_String_AnySCharacter] ('mango,orange,pineapple',',')

User Defined Function Split String SQL Server 5

Example of using multiple special characters in the string:

While passing more than one special character in the string, the user-defined function we created is going to accept just the special character we mentioned in the @delimiter parameter.

select * from [fn_Split_String_AnySCharacter] ('mango,orange%pineapple',',')