Create string split function in SQL Server (Table-valued Functions (All time return table))

Learn how to split string according to specific character using function in SQL Server. Below is the example of complete SQL function that split string.

USE [ProjectName]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[SplitString] ( @stringToSplit VARCHAR(MAX) )

RETURNS

 @returnList TABLE ([Name] [nvarchar] (500))

AS

BEGIN

 DECLARE @name NVARCHAR(255)

 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0

 BEGIN

  SELECT @pos  = CHARINDEX(',', @stringToSplit)  

  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 

  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)

 END

 INSERT INTO @returnList

 SELECT @stringToSplit

 RETURN

END

Call : (SELECT * FROM dbo.SplitString(@desc1))