Split string in Sql Server

If you are handling most of the code by procedures or functions. Then this topic will helpful for you So let's discuss about How to split string in SQL Server.

I want to separate string by a delimiter . Delimiter can be a any character like comma(','), Parentheses('('), Braces ('[ ') , Angle brackets('<') etc. Lets take a example of string I want to separated it by comma.

Input Text: "text 1, text 2, text 3 , text 4, text 5"
 
Output in table format like.

item value
text 1
text 2
text 3
text 4
text 5

Step 1- Initially add a new Table-Valued Function

CREATE FUNCTION [dbo].[uf_SplitString]
    (
      @InputString VARCHAR(MAX) ,
      @Delimater VARCHAR(10)
    )
RETURNS @otTemp TABLE ( itemvalue VARCHAR(max) )
AS 
    BEGIN
        DECLARE @sTemp VARCHAR(max)
 
        WHILE LEN(@InputString) > 0 
            BEGIN
                SET @sTemp = LEFT(@InputString,
                                  ISNULL(NULLIF(CHARINDEX(@Delimater,
                                                          @InputString) - 1,
                                                -1), LEN(@InputString)))
                SET @InputString = SUBSTRING(@InputString,
                                             ISNULL(NULLIF(CHARINDEX(@Delimater,
                                                              @InputString), 0),
                                                    LEN(@InputString)) + 1,
                                             LEN(@InputString))
                INSERT  INTO @otTemp
                VALUES  ( @sTemp )
            END
 
        RETURN
    END

Step 2- Now Run function

SELECT * FROM [dbo].[uf_SplitString] (
  'text 1, text 2, text 3 , text 4, text 5'
  ,',')
GO