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.
  1. USE [TestDB]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. CREATE FUNCTION [dbo].[SplitString]  
  8. (  
  9.    @Input NVARCHAR(MAX),  
  10.    @Character CHAR(1)  
  11. )  
  12. RETURNS @Output TABLE (  
  13.    Item NVARCHAR(1000)  
  14. )  
  15. AS  
  16. BEGIN  
  17. DECLARE @StartIndex INT, @EndIndex INT  
  18. SET @StartIndex = 1  
  19. IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character  
  20. BEGIN  
  21. SET @Input = @Input + @Character  
  22. END  
  23. WHILE CHARINDEX(@Character, @Input) > 0  
  24. BEGIN  
  25. SET @EndIndex = CHARINDEX(@Character, @Input)  
  26. INSERT INTO @Output(Item)  
  27. SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  
  28. SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
  29. END  
  30. RETURN  
  31. 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,
  1. 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.
 
Split String Using Table Valued Function in SQL Server