Split Any Delimited String in SQL

Introduction

Often we come across such scenarios where we need to pass data as string, for example (Apple, Mango, Orange, Pineapple) to database and then store each item of string in database as separate rows in respective table. Thus it leads us to identify a mechanism to be able to split string based on some delimited characters and use the result accordingly.

Using the Code

With the use of T-SQL to split a delimited string with any delimited characters, the problem can be solved easily.

Create a SQL function as below:

// sql function which accepts string and delimiter character and return items in table.

CREATE FUNCTION [dbo].[SplitString] (@InputString NVARCHAR(MAX),@delimiter CHAR(1))
RETURNS @tbl TABLE (Item NVARCHAR(50) NOT NULL)
AS
BEGIN
  DECLARE @StartIndex int = 0,
          @NextIndex int = 1,
          @ItemLen int

  WHILE @NextIndex > 0
  BEGIN
    SELECT @NextIndex = CHARINDEX(@delimiter, @InputString, @StartIndex + 1)
    SELECT @ItemLen = CASE
                          WHEN @NextIndex > 0 THEN @NextIndex
                          ELSE LEN(@InputString) + 1
                      END - @StartIndex - 1

    INSERT @tbl (Item)
    VALUES (CONVERT(varchar(50), SUBSTRING(@InputString, @StartIndex + 1, @ItemLen)))
    
    SELECT @StartIndex = @NextIndex

  END

  RETURN

END

Testing the Above SQL Function with Samples Input

Execute the query:

SELECT * FROM [SplitString]('Apple-Dell-HP-Lenovo-Sony','-')

OUTPUT

	Item
1	Apple
2	Dell
3	HP
4	Lenovo
5	Sony

Points of Interest

I hope the tip was easy to understand and implement.

History

• 11^th September, 2015: Initial version