Extract and process substring between varying numbers of delimiters in a variable length string

Question

I have a table dates with a column SENTDATE where each row has a varying number of dates in it, separated by a semi colon (;). The dates are currently in the format YYYYMMDD - for example:

20240529;20240626;20240626;20240626;20240626;20240626
20240530;20240620
20240605
20240523;20240523

I'm attempting to convert each of these dates into the format DD/MM/YYYY and keep them delimited by the semicolon so they look like this:

29/05/2024;26/06/2024;26/06/2024;26/06/2024;26/06/2024
30/05/2024;20/06/2024

Unfortunately, I am using SQL Server 2005, so in order to change the formats I need to use SUBSTRING and then combine the different portions of the dates, interspersed with forward slashes:

SUBSTRING(SENTDATE, 1, 4) + '/' + SUBSTRING(SENTDATE, 5, 2) + '/' + SUBSTRING(SENTDATE, 7, 2)

but that's no issue.

Would you know of a way of being able to process each date between the semi colons? I'm assuming it'll be some sort of split and then recombination of the string?

I haven't actually tried anything here. I could use PARSENAME but I have no clue how to do that with varying numbers of delimiters.

Thank you.

Answer 1

You should really change the schema of your table to comply with basic database normalisation rules. However, if you have to deal with this poor schema you can do the following:

Sample Data and Query

DECLARE @t TABLE (Id INT , SENTDATE VARCHAR(1000))

INSERT INTO @t (Id , SENTDATE)
VALUES 
 ( 1 , '20240529;20240626;20240626;20240626;20240626;20240626')
,( 2 , '20240530;20240620')
,( 3 , '20240605')
,( 4 , '20240523;20240523');


WITH CTE AS 
(
     SELECT A.Id  
         , CONVERT(VARCHAR(10) , CAST(Split.a.value('.', 'VARCHAR(100)') AS DATETIME) , 103) AS SENTDATE  
     FROM  
     (
         SELECT Id,  
             CAST ('<Row>' + REPLACE(SENTDATE, ';', '</Row><Row>') + '</Row>' AS XML) AS Data  
         FROM  @t
     ) AS A CROSS APPLY Data.nodes ('/Row') AS Split(a)
 )
SELECT  ID
       ,STUFF((SELECT '; ' + CAST(SENTDATE AS VARCHAR(10)) [text()]
         FROM CTE 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM CTE t
GROUP BY ID

Output

+----+-------------------------------------------------------------------------+
| ID |                               List_Output                               |
+----+-------------------------------------------------------------------------+
|  1 |  29/05/2024; 26/06/2024; 26/06/2024; 26/06/2024; 26/06/2024; 26/06/2024 |
|  2 |  30/05/2024; 20/06/2024                                                 |
|  3 |  05/06/2024                                                             |
|  4 |  23/05/2024; 23/05/2024                                                 |
+----+-------------------------------------------------------------------------+

Note I am pretty sure that concepts like FOR XML PATH, CROSS APPLY and CTE were introduced in SQL Server 2005, so this solution should work for you.

Answer 2

Please try the following solution that is using tokenization via SQL Server's XML and XQuery.

It will work starting from SQL Server 2005 onwards.

Notable points:

• CROSS APPLY is converting input string into XML for tokenization.
• XQuery .query() method converting individual date values into a desired format in the FLWOR expression.
• XQuery .value() method in the SELECT clause is reverting XML back to a string.

SQL

DECLARE @tbl TABLE (Id INT IDENTITY PRIMARY KEY, SENTDATE VARCHAR(1000));
INSERT INTO @tbl (SENTDATE) VALUES 
('20240529;20240626;20240626;20240626;20240626;20240626'),
('20240530;20240620'),
('20240605'),
('20240523;20240523');

DECLARE @separator CHAR(1) = ';';

SELECT t.*, c
    , REPLACE(c.query('data(/root/r)').value('.','VARCHAR(1000)'), SPACE(1), @separator) AS result
FROM @tbl AS t
CROSS APPLY (SELECT CAST('<root><r><![CDATA[' + 
    REPLACE(SENTDATE, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML).query('<root>{
        for $x in /root/r/text()
        return <r>{concat(substring($x,7,2),"/", substring($x,5,2),"/",substring($x,1,4))}</r>
    }</root>')) AS t1(c);

Output

Id	SENTDATE	result
1	20240529;20240626;20240626;20240626;20240626;20240626	29/05/2024;26/06/2024;26/06/2024;26/06/2024;26/06/2024;26/06/2024
2	20240530;20240620	30/05/2024;20/06/2024
3	20240605	05/06/2024
4	20240523;20240523	23/05/2024;23/05/2024