Split and convert Comma Separated String or any Delimited String to Table in SQL Server

Hi friends,in this article I will explain about How to Split and convert Comma Separated String or any Delimited String to Table in SQL Server

In this article I will explain with examples, how to split and convert a comma separated / delimited string to a table using Split function in SQL Server 2005, 2008 and 2012 versions. The string containing words or letters or numbers separated (delimited) by comma or underscore or  plus(+) ,etc,. will be split into Table values.

I will also explain how to use the Split function to split a string in a SQL Query or Stored Procedures in SQL Server 2005, 2008 and 2012 versions.

--Split and convert Comma Separated (Delimited) String to Table in SQL Server CREATE FUNCTION [dbo].[SplitString] (@InputString NVARCHAR(MAX),@delimiter CHAR(1)) RETURNS @tbl TABLE (   Item NVARCHAR(50) NOT NULL ) AS BEGIN   DECLARE @StartIndex int,           @NextIndex int,           @ItemLen int   SELECT     @StartIndex = 0,     @NextIndex = 1   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

Example 1:

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

Example 2:

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

The output of the above two queries will be resulted as 

Example 3:

SELECT * FROM [SplitString]('1,2,3,4,5')

The output of the above Query is as shown in the below figure.