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

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.

Solution

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.