separate the string by delimiter and assign to variable - sql

Question

I have string 'sunday,monday,thursday' i want to separate them and assign them to variable like @day1, @day2, @day3. Sometime number of days in sting will vary. How to achieve this in sql?

Answer 1

Try the following

DECLARE
  @day1 varchar(10),
  @day2 varchar(10),
  @day3 varchar(10),
  @day4 varchar(10)

DECLARE @Split TABLE (N int IDENTITY, S varchar(200))

INSERT @Split (S) SELECT value FROM STRING_SPLIT('sunday,monday,thursday',',')

SELECT
  @day1=MAX(CASE WHEN N=1 THEN S END),
  @day2=MAX(CASE WHEN N=2 THEN S END),
  @day3=MAX(CASE WHEN N=3 THEN S END),
  @day4=MAX(CASE WHEN N=4 THEN S END) -- is null
FROM @Split

-- check
SELECT @day1,@day2,@day3,@day4

A variant with CTE

DECLARE
  @day1 varchar(10),
  @day2 varchar(10),
  @day3 varchar(10),
  @day4 varchar(10),
  @day5 varchar(10),
  @day6 varchar(10),
  @day7 varchar(10)

DECLARE @input varchar(100)='sunday,monday,thursday'

;WITH sCTE AS(
  SELECT
    1 n,
    NULLIF(LEFT(@input,ISNULL(NULLIF(CHARINDEX(',',@input)-1,-1),LEN(@input))),'') s,
    IIF(CHARINDEX(',',@input)=0,'',RIGHT(@input,LEN(@input)-CHARINDEX(',',@input))) p
  UNION ALL
  SELECT
    n+1,
    LEFT(p,ISNULL(NULLIF(CHARINDEX(',',p)-1,-1),LEN(p))) s,
    IIF(CHARINDEX(',',p)=0,'',RIGHT(p,LEN(p)-CHARINDEX(',',p))) p
  FROM sCTE
  WHERE p<>''
)
SELECT
  @day1=MAX(CASE WHEN N=1 THEN S END),
  @day2=MAX(CASE WHEN N=2 THEN S END),
  @day3=MAX(CASE WHEN N=3 THEN S END),
  @day4=MAX(CASE WHEN N=4 THEN S END),
  @day5=MAX(CASE WHEN N=4 THEN S END),
  @day6=MAX(CASE WHEN N=4 THEN S END),
  @day7=MAX(CASE WHEN N=4 THEN S END)
FROM sCTE

-- check
SELECT @day1,@day2,@day3,@day4,@day5,@day6,@day7

A variant if you have an input string like monday,tuesday,friday and if you want to set it as @day2=monday; @day3=tuesday; @day6=friday

DECLARE
  @day1 varchar(10),
  @day2 varchar(10),
  @day3 varchar(10),
  @day4 varchar(10),
  @day5 varchar(10),
  @day6 varchar(10),
  @day7 varchar(10)

DECLARE @input varchar(100)='monday,tuesday,friday'

SET @day1=IIF(CHARINDEX('sunday',@input)>0,'sunday',NULL)
SET @day2=IIF(CHARINDEX('monday',@input)>0,'monday',NULL)
SET @day3=IIF(CHARINDEX('tuesday',@input)>0,'tuesday',NULL)
SET @day4=IIF(CHARINDEX('wednesday',@input)>0,'wednesday',NULL)
SET @day5=IIF(CHARINDEX('thursday',@input)>0,'thursday',NULL)
SET @day6=IIF(CHARINDEX('friday',@input)>0,'friday',NULL)
SET @day7=IIF(CHARINDEX('saturday',@input)>0,'saturday',NULL)

-- check
SELECT @day1,@day2,@day3,@day4,@day5,@day6,@day7