Parsing Comma Separated Values (CSV) Into Table in T-SQL
To parse the Comma Separated Values (CSV) into Table in Sql Sever, use the following
STEP 1
Execute the following procedure in your Sql Server window.
The fn_CommaSeparatedStringToTable function accepts comma separated string and a value('Y' or 'N') to include NULL and EMPTY strings in the result set
ALTER FUNCTION [dbo].[fn_CommaSeparatedStringToTable]
(
@CommaSeparatedValues VARCHAR(MAX),
@IncludeEmptyStrings CHAR(1)
)
RETURNS @Item TABLE
(
RowId int IDENTITY(1, 1) NOT NULL,
Value VARCHAR(200)
)
AS
BEGIN
DECLARE @IndefOfComma int,@Value VARCHAR(200),@StartPos bigint,@EndPos bigint,@LengthOfString int, @ReachedEnd Char(1)
SET @StartPos=1
SET @EndPos=0
SET @LengthOfString=LEN(@CommaSeparatedValues)
SET @ReachedEnd='N'
WHILE @ReachedEnd<>'Y'
BEGIN
SET @EndPos=CHARINDEX(',',@CommaSeparatedValues,@StartPos)
IF @EndPos>0
BEGIN
SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@EndPos-@StartPos)
SET @StartPos=@EndPos+1
END
ELSE
BEGIN
SEt @ReachedEnd='Y'
SET @Value = SUBSTRING(@CommaSeparatedValues, @StartPos,@LengthOfString-(@StartPos-1))
END
IF(@Value<>'' OR @IncludeEmptyStrings='Y')
INSERT INTO @Item(Value) VALUES(@Value)
END
RETURN
END
STEP 2
To access the function, use the following query
Include Empty or Null String in the result
SELECT * FROM [dbo].[fn_CommaSeparatedStringToTable]
('ALICE,BRAD,JOHN,JOE,MIC,SCAIF,JEMY, ,','Y')
Do not include Empty or Null String in the result
SELECT * FROM [dbo].[fn_CommaSeparatedStringToTable]
('ALICE,BRAD,JOHN,JOE,MIC,SCAIF,JEMY, ,','N')
Alternate Titles: Parsing CSV,Creating List from Comma Separated Values, creating Table From Comma Separated Values CSV