Split String Using Table Valued Function In SQL Server

Learn how to split a string by creating a user defined function in SQL Server.

Here we will learn how to split a string with an example in SQL and we will create a user defined function in SQL Server to do so. The string containing words or letters separated (delimited) by comma will be split into table values.

 

Step 1

 

First create a table valued function or user defined function using MS SQL Server.

 

Write the following SQL code to create the own user defined split function. Don't forget to change your database name in the first USE syntax.

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitString]
(
   @Input NVARCHAR(MAX),
   @Character CHAR(1)
)
RETURNS @Output TABLE (
   Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END

Note

@Input – The string to be splitted.

@Character – The character that is used to separate (delimited) the words or letters.

 

Step 2

 

Call or use user defined function to split function,

 

Write the following code to call or use the user defined function to split the string,

SELECT Item FROM DBO.SPLITSTRING('Name ,Age ,Gender ,Address ,Email ,Phone',',');

If you observe above example we are splitting string with special characters comma “,”.

 

Output

 

Following is the result of split strings.