Convert comma separated string to table in SQL

Description:-

How to convert comma separated nvarchar to table records in sql. Create function to convert comma Delimited value string to table.

Incoming search terms

Convert Comma Separated String to Table in SQL, How to convert comma separated NVARCHAR to table, separate comma separated values and store in table in sql, (Delimited) String to Table in SQL Server, Comma Delimited Value To Table – SQL Server, Convert comma separated string to table, Split a string to a table using SQL, Converting Comma Separated Value to Rows, Convert comma separated string to list sql server, SQL convert table to comma delimited string.

Following are the steps to Convert comma separated string to table in SQL

Create function:- 

Create new function in your database and then execute it with suitable parameter. Over here we used two parameter one is String and another one is Delimiter that used for split the string.

CREATE FUNCTION [dbo].[CSVToTable]
(
@String VARCHAR(8000),
@Delimiter CHAR(1)
)    

RETURNS @temptable TABLE (
items VARCHAR(8000)
)

AS
BEGIN
 DECLARE @idx INT
 DECLARE @slice VARCHAR(8000)

 SELECT @idx = 1
 IF len(@String)<1 or @String is null  RETURN
 
 WHILE @idx!= 0
 BEGIN
  SET @idx = charindex(@Delimiter,@String)
  IF @idx!=0
   SET @slice = left(@String,@idx - 1)
  ELSE
   SET @slice = @String

  IF(len(@slice)>0)
   INSERT INTO @temptable(Items) VALUES( @slice  )

  SET @String = right(@String,len(@String) - @idx)
  IF len(@String) = 0 BREAK
 END
RETURN
END

EXECUTE FUNCTION

After creating function you need to execute it. Pass only two parameter “String” and “Delimiter” as per created function.

SELECT *FROM CSVToTable('A-B-C-D','-')

Output:-

A
B
C
D

Author : Umesh Patel

Related Posts