Return date from value exploded in some columns on SQL Server

Question

I would like to return the date for each string exploded by slash on SQL Server.

My database table:

id | date       | col1        | col2      | col3  | col4
---+------------+-------------+-----------+-------+------------
1  | 2017-04-02 | /txt1/txt2  |           |       |
2  | 2017-04-03 |             | /txt1/txt4|       |
3  | 2017-04-04 |             |/txt2/txt3 |       |
4  | 2017-04-05 |             |/txt4      |       |/txt5/txt6

Result wanted:

2017-04-02 txt1
2017-04-02 txt2
2017-04-03 txt1
2017-04-03 txt4
2017-04-04 txt2
2017-04-04 txt3
2017-04-05 txt4
2017-04-05 txt5
2017-04-05 txt6

Thank you Pierre

Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Apr 02 '17 at 11:53
@ZoharPeled it can be the case though that OP is not responsible for the design of the database. — Giorgos Altanis, Apr 02 '17 at 11:55
@GiorgosAltanis Yes, that's possible. Still this is something to know even if the op didn't plan the database and can't change the structure. — Zohar Peled, Apr 02 '17 at 11:57
Thanks for your advice, but i can't change to database structure for the moment. I don't know how do this in SQL. Previously i was made the explode in PHP. — Pierre001, Apr 02 '17 at 12:03

score 1 · Accepted Answer · answered Apr 02 '17 at 12:09

1

Assuming that you cannot use STRING_SPLIT() function (available from SQL Server 2016) you will first need a function to tokenise a varchar column. There are hundreds of them in the web, I leave it up to you though to pick the most suitable and well-tested.

For my example I will go with this, which works for your sample:

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Applying the split function

select t.date, x.splitdata 
from test t 
cross apply dbo.fnSplitString(
   coalesce(col1, '') + coalesce(col2, '') 
   + coalesce(col3, '') + coalesce(col4, '')
, '/') x
where coalesce(x.splitdata, '') <> ''

rextester demo

Note that in the demo I used the Id column instead of the date column.

answered Apr 02 '17 at 12:09

Giorgos Altanis

2,742
1
13
14

Aaron Bertrand's [Split strings the right way – or the next best way.](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) – Zohar Peled Apr 02 '17 at 12:13
Great reference @ZoharPeled. – Giorgos Altanis Apr 02 '17 at 12:16
@GiorgosAltanis Well worth the effort. You may be surprised on the performance gains. – John Cappelletti Apr 02 '17 at 12:17
i've tested your query. it's works but i have some table who i need to use this query and i would like to use a query like : SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable' AND TABLE_SCHEMA='dbo' AND COLUMN_NAME != 'id' AND COLUMN_NAME != 'date' for retrieve the list of fields in COALESCE function. Thanks – Pierre001 Apr 02 '17 at 13:05
Thanks for your comment; I am glad that the solution works, so I suggest you accept the answer (since it solved the problem) and create a new question for any other problem you might have. – Giorgos Altanis Apr 02 '17 at 13:57

John Cappelletti · Answer 2 · 2017-04-02T16:27:52.647

Everyone should have a good split/parse function as Zohar Peled linked to.

Another option if you can't use (or want) a UDF.

Example

Select A.Date
      ,C.RetVal
 From  YourTable A
 Cross Apply (values (col1),(col2),(col3),(col4)) B (Value)
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(B.Value,'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) C
 Where C.RetVal is not null

Returns

Date        RetVal
2017-04-02  txt1
2017-04-02  txt2
2017-04-03  txt1
2017-04-03  txt4
...

Return date from value exploded in some columns on SQL Server

2 Answers2