Rajeev Pentyala – Microsoft Power Platform

Assume that you have a String with Comma separated value and wanted to split and get the collection in SQL.

My string looks ‘A,B,C,D, ‘ and I want output as

Value

A

B

C

D

Approach

The approach I am following here is

• Declare a Temporary table
• Split the string
•  Insert the split values in to Temporary table

Query

DECLARE @MyValuevarchar(100)

DECLARE @posINT

DECLARE @lenINT

DECLARE @valuevarchar(8000)

SET @pos= 0

SET @len= 0

— Set the Comma separated value

SET @MyValue=‘A,B,C,D,’

— Declare Temporary Table to store split values

DECLARE @MyTempTbl TABLE (

[Value] [nvarchar](100)

)

WHILE CHARINDEX(‘,’,@MyValue,@pos+1)>0

BEGIN

SET @len=CHARINDEX(‘,’,@MyValue,@pos+1)–@pos

SET @value=SUBSTRING(@MyValue,@pos,@len)

— Insert the splitter value in to Temporary Table

INSERT INTO @MyTempTbl Values(@value)

SET @pos=CHARINDEX(‘,’,@MyValue,@pos+@len)+1

END

— Get the resultset

SELECT * FROM @MyTempTbl

Output you get as below

String Split Result

Note –

• My requirement was to split the string and use in ‘IN’ condition, hence I created a Temporary table.
• Also you can replace ‘,’ with any of the character

🙂