Query to update strings using string_split function

Question

I am trying to update column in table where data is in below format:

Id | ColA
----------
1    Peter,John:Ryan,Jack:Evans,Chris
2    Peter,John:Ryan,Jack
3    Hank,Tom
4    
5    Cruise,Tom

I need to split the string by ':' and remove ',' and need to reverse the name and again append the same data separated by: and finally data should be as shown

Id | ColA
----------
1    John Peter:Jack Ryan:Chris Evans
2    John Peter:Jack Ryan
3    Tom Hank
4    
5    Tom Cruise

Please let me know how can we achieve this I tried to use Replace and Substring but how can we do it if we have data some are separated by two colon and some are separated by single colon. Is there any way to identify and achieve the data in the above formatted one.

Answer 1

Something like this should work:

CREATE TABLE YourTableNameHere (
    Id int NULL 
    ,ColA varchar(1000) NULL 
);
INSERT INTO YourTableNameHere (Id,ColA) VALUES
(1, 'Peter,John:Ryan,Jack:Evans,Chris')
,(2, 'Peter,John:Ryan,Jack')
,(3, 'Hank,Tom')
,(4, '')
,(5, 'Cruise,Tom');

SELECT 
    tbl.Id 
    ,STUFF((SELECT 
                CONCAT(':'
                    ,RIGHT(REPLACE(ss.value, ',', ' '), LEN(REPLACE(ss.value, ',', ' ')) -  CHARINDEX(' ', REPLACE(ss.value, ',', ' '), 1)) /*first name*/ 
                    ,' '
                    ,CASE WHEN CHARINDEX(',', ss.value, 1) > 1 THEN  LEFT(REPLACE(ss.value, ',', ' '), CHARINDEX(' ', REPLACE(ss.value, ',', ' '), 1) - 1) /*last name*/ ELSE '' END)
            FROM 
                YourTableNameHere AS tbl_inner
                CROSS APPLY string_split(tbl_inner.ColA, ':') AS ss
            WHERE 
                tbl_inner.Id = tbl.Id
            FOR XML PATH('')), 1, 1, '') AS ColA 
FROM 
    YourTableNameHere AS tbl; 

This uses the string_split function within a FOR XML clause to split the values in ColA by the : character, then replace the , with a space, parse to the left and right of the space, then recombine the parsed values delimited by a : character.

One thing to note here, per Microsoft the output of string_split is not guaranteed to be in the same order as the input:

Note
The order of the output may vary as the order is not guaranteed to match the order of the substrings in the input string.

So in order to guarantee the output of this function is going to concatenate the names back in the same order that they existed in the input column you would either need to implement your own function to split the string or come up with some criteria for combining them in a certain order. For example, you could recombine them in alphabetical order by adding ORDER BY ss.value to the inner query for ColA in the final result set. In my testing using your input the final values were ordered the same as the input column, but it is worth noting that that behaviour is not guaranteed and in order to guarantee it then you need to do more work.

Answer 2

Here is a solution for SQL Server 2008 onwards.

It is based on XML and XQuery.

Using XQuery's FLWOR expression allows to tokenize odd vs. even XML elements. The rest is just a couple of the REPLACE() function calls to compose the desired output.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
INSERT INTO @tbl (tokens) VALUES
('Peter,John:Ryan,Jack:Evans,Chris'),
('Peter,John:Ryan,Jack'),
('Hank,Tom'),
(''),
('Cruise,Tom');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ':'
    , @comma CHAR(1) = ',';

SELECT ID, tokens
   , REPLACE(REPLACE(c.query('
       for $x in /root/r[position() mod 2 eq 0]
       let $pos := count(root/r[. << $x]) 
       return concat($x, sql:variable("@comma"), (/root/r[$pos])[1])
       ').value('text()[1]', 'VARCHAR(8000)')
       , SPACE(1), @separator), @comma, SPACE(1)) AS result
FROM @tbl
    CROSS APPLY (SELECT CAST('<root><r><![CDATA[' + 
            REPLACE(REPLACE(tokens,@comma,@separator), @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

Output

+----+----------------------------------+----------------------------------+
| ID |              tokens              |              result              |
+----+----------------------------------+----------------------------------+
|  1 | Peter,John:Ryan,Jack:Evans,Chris | John Peter:Jack Ryan:Chris Evans |
|  2 | Peter,John:Ryan,Jack             | John Peter:Jack Ryan             |
|  3 | Hank,Tom                         | Tom Hank                         |
|  4 |                                  | NULL                             |
|  5 | Cruise,Tom                       | Tom Cruise                       |
+----+----------------------------------+----------------------------------+

SQL #2 (don't try it, it won't work)

Unfortunately, SQL Server doesn't fully support even XQuery 1.0 standard. XQuery 3.1 is the latest standard. XQuery 1.0 functions fn:substring-after() and fn:substring-before() are badly missing.

In a dream world a solution would be much simpler, along the following:

SELECT *
    , c.query('
    for $x in /root/r
    return concat(fn:substring-after($x, ","), ",", fn:substring-before($x, ","))
    ') 
FROM @tbl
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
            REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML)) AS t1(c);

Please up-vote the following suggestion to improve SQL Server: SQL Server vNext (post 2019) and NoSQL functionality

It became one of the most popular requests for SQL Server. The current voting tally is 590 and counting.