SQL - Split string multiple values, multiple values

Question

I'm looking to split a string of delimited dates into their respective values and place them within columns of a results table. I've had some success with the following, not quite there for my specific application SQL - Split string to columns by multiple delimiters

String (from & to dates)
tblTempDates.tmpString
2019-2-11--2019-2-15,2019-2-20--2019-2-20,2019-2-23--2019-2-23,2019-3-19--2019-3-24

Delimiters
-- seperates from & to
, new record

Output required
tblTempDates2
dtFrom        dtTo
2019-02-11    2019-02-15
2019-02-20    2019-02-20
2019-02-23    2019-02-23
2019-02-19    2019-03-24

Many thanks in advance :-)

Are you really using -- as a delimiter? This is ill advised as -- denotes a comment — JohnHC
– JohnHC, Commented Feb 26, 2019 at 10:55
Thanks for the comment - I'm not precious on the delimiter character as such. — BigIWT
– BigIWT, Commented Feb 26, 2019 at 11:07

Ajan Balakumaran · Accepted Answer · 2019-02-26 11:53:54Z

1

Can use while statement to split the csv,

    declare @a table(datefromto varchar(500))

    declare @b table(fromdate varchar(500),todate varchar(500))

    insert into @a values ('2019-2-11--2019-2-15,2019-2-20--2019-2-20,2019-2-23--2019-2-23,2019-3-19--2019-3-24');

    declare @datefromto varchar(500) = (select max(datefromto) from @a)

    declare @datesplit int
    set @datesplit= (SELECT charindex(',',@datefromto))



    WHILE (SELECT charindex(',',@datefromto) FROM @a) <> 0
    BEGIN  



       insert into @b select Left(left(@datefromto,@datesplit),charindex('--',@datefromto)-1) ,
       Right(left(@datefromto,@datesplit-1),charindex('--',@datefromto)-1) from @a


       set @datesplit= (SELECT charindex(',',@datefromto))
       set @datefromto= right(@datefromto,len(@datefromto)-@datesplit)

    END  
       insert into @b select Left(left(@datefromto,@datesplit),charindex('--',@datefromto)-1) ,
       Right(left(@datefromto,@datesplit-1),charindex('--',@datefromto)-1) from @a

    select * from @b

edited Feb 26, 2019 at 11:53

answered Feb 26, 2019 at 10:58

Ajan Balakumaran

1,6491 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

BigIWT Over a year ago

This partially works - in that it gets the very first date and the very last but nothing in between (2019-02-11 ... 2019-03-24) - so a single row only

Tim Biegeleisen Over a year ago

It also leaves out the splitting CSV to rows step; not a minor step.

Ajan Balakumaran Over a year ago

Understood your problem involves the comma separated list as well, I have modified the query with a while loop can you give it a try.

Ajan Balakumaran Over a year ago

@BigIWT Were you able to try this?

BigIWT Over a year ago

Hi, yes just now - we're almost there actually. It works but needs to be able to handle day & month lengths - some are single digits, some are double (2019-1-1) (2019-1-31) (2019-10-1) (2019-10-30) - thanks in advance

|

Tim Biegeleisen · Accepted Answer · 2019-02-26 11:05:09Z

0

Here is one nice way to do this using STRING_SPLIT, if your version of SQL Server supports it:

SELECT
    LEFT(value, CHARINDEX('--', value) - 1) AS dtFrom,
    SUBSTRING(value, CHARINDEX('--', value) + 2, 20) AS dtTo
FROM
(
    SELECT STRING_SPLIT(input, ',')
    FROM yourTable
) t;

This approach first splits each from/to date substring to a separate row by comma using STRING_SPLIT. Then, it uses base string functions to isolate the from and to dates.

edited Feb 26, 2019 at 11:05

answered Feb 26, 2019 at 10:59

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

3 Comments

BigIWT Over a year ago

Thanks - using SQL 2014

Tim Biegeleisen Over a year ago

Then you will have to use some other method to split each CSV value to a separate row. But, my outer query logic should still work.

BigIWT Over a year ago

I have a similar SplitString function, when using, your suggested code, it spits out the same dates repeatedly, as opposed to each row being seperated. Also, the date charindex will change depending on the day/month length ... thanks

Gottfried Lesigang · Accepted Answer · 2019-02-26 12:06:16Z

with versions before STRING_SPLIT() (or OPENJSON) I'd suggest this approach:

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(1000));
INSERT INTO @tbl VALUES('2019-2-11--2019-2-15,2019-2-20--2019-2-20,2019-2-23--2019-2-23,2019-3-19--2019-3-24');

WITH Casted AS
(
    SELECT *
         , CAST('<x><y>' + REPLACE(REPLACE(YourString,'--','</y><y>'),',','</y></x><x><y>') + '</y></x>' AS XML) AsXml
    FROM @tbl 
)
SELECT  ID 
       ,x.value('y[1]','date') AS Date1
       ,x.value('y[2]','date') AS Date2
FROM Casted
CROSS APPLY AsXml.nodes('/x') A(x);

The idea in short:

Some string replacements transform your multi-level-CSV into an XML like this

<x>
  <y>2019-2-11</y>
  <y>2019-2-15</y>
</x>
<x>
  <y>2019-2-20</y>
  <y>2019-2-20</y>
</x>
<x>
  <y>2019-2-23</y>
  <y>2019-2-23</y>
</x>
<x>
  <y>2019-3-19</y>
  <y>2019-3-24</y>
</x>

We can then use .nodes() to get all repeating <x> as derived table. Then we use .value() to fetch the <y> by their position.

Collectives™ on Stack Overflow

SQL - Split string multiple values, multiple values

3 Answers 3

8 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related