How to Split a String into Variables in Sql

split the string and store it in three different variables in sql server

if they are always in the same order and same separator then there is not need for a loop, this will do all the job:

DECLARE @siteIds varchar(100)
DECLARE @city varchar(100), @state varchar(100), @zip varchar(100)

SET @siteIds = 'OMAHA  NE  68117'

select @city=(parsename(replace(@siteIds,'  ','.'),3)),
       @state=(parsename(replace(@siteIds,'  ','.'),2)),
       @zip=(parsename(replace(@siteIds,'  ','.'),1))

STRING_SPLIT to Multiple Variables

Convert to JSON

Perhaps the easiest solution would be to convert the string to a JSON array and access the items by position :

declare @text varchar(200)='1.25,5.34,6.9,8.6547,2.3'
declare @json varchar(202)='[' + @text + ']'

declare @var1 numeric(18,5)= JSON_VALUE(@json,'$[0]')
declare @var2 numeric(18,5)= JSON_VALUE(@json,'$[1]')

select @var1,@var2

Store in Table variable but lose the order

A set-based solution would be to store the items in a table variable BUT the order will probably be lost as Raymond Nijland noted :

declare @text varchar(200)='1.25,5.34,6.9,8.6547,2.3'

declare @values table (id int identity,val varchar(20))

insert into @values (val)
select trim(value) from STRING_SPLIT(@text,',') x

insert into SomeTable (value1)
select val 
from @values where ID=1

The only order that can be imposed is ascending or descending using ORDER BY, which assumes the input's order doesn't matter. Not very helpful.

separate the string by delimiter and assign to variable - sql

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

Split string of variable length, variable delimiters

You can also use a string splitter. Here is an excellent one that works with your version. DelimitedSplit8K

Now we need some sample data.

declare @Something table
(
    Category varchar(100)
)

insert @Something values
('Zoo - Animals - Lions')
, ('Zoo - Personnel')
, ('Zoo - Operating Costs - Power / Cooling')

Now that we have a function and sample data the code for this is quite nice and tidy.

select s.Category
    , Location = max(case when x.ItemNumber = 1 then Item end)
    , Category = max(case when x.ItemNumber = 2 then Item end)
    , SubCategory = max(case when x.ItemNumber = 3 then Item end)
from @Something s
cross apply dbo.DelimitedSplit8K(s.Category, '-') x
group by s.Category

And this will return:

Category                                |Location|Category       |SubCategory
Zoo - Animals - Lions                   |Zoo     |Animals        |Lions
Zoo - Operating Costs - Power / Cooling |Zoo     |Operating Costs|Power / Cooling
Zoo - Personnel                         |Zoo     |Personnel      |NULL

Two Split String Merging into one table variable

SELECT    t1.ID
        , t2.FID
FROM (
        Select Value As ID
                , ROW_NUMBER() OVER (ORDER BY  Value) rn       
        from STRING_SPLIT(@id,'#')
    ) t1
INNER JOIN 
        (
           Select Value AS FID
                , ROW_NUMBER() OVER (ORDER BY  Value) rn 
           from STRING_SPLIT(@fid,'#')
        ) t2 ON t1.rn = t2.rn 

Splitting an SQL string into multiple strings

Assuming that there will always be 3 email addresses - the following seems to work;

DECLARE @Email VARCHAR(100),
        @Email2 VARCHAR(100),
        @Email3 VARCHAR(100)

SET @Email = 'joebloggs@gmailcom;jimbowen@aol.com;dannybaker@msn.com'

SELECT   @Email     = LEFT(@Email, CHARINDEX(';', @Email) - 1)
        ,@Email2    = SUBSTRING (   
                                    @Email, 
                                    CHARINDEX(';', @Email) + 1, 
                                    CHARINDEX(';', @Email, CHARINDEX(';', @Email) + 1) - LEN(LEFT(@Email, CHARINDEX(';', @Email) )) - 1
                                )
        ,@Email3    = RIGHT(@Email, CHARINDEX(';', @Email)-1)

---

Related Topics