SQL Server: Add slash every 2 characters

Question

Is there anyway to get this more elegantly?

SELECT 
    substring(im_notext1.nclassif, 1, 2) + '/' +
    substring(im_notext1.nclassif, 3, 2) + '/'+
    substring(im_notext1.nclassif, 5, 2) + '/' +
    substring(im_notext1.nclassif, 7, 2) + '/' +
    substring(im_notext1.nclassif, 9, 2) + '/' +
    substring(im_notext1.nclassif, 11, 2) + '/' +
    substring(im_notext1.nclassif, 13, 2) AS new_codi_nivell
FROM 
    im_notext1

Shortly, I need to insert a slash every second character.

Any ideas?

I would have sworn I saw MySQL tagged earlier. `concat_ws(...)` is available in SQL server too though. — , Nov 10 '20 at 13:30

score 0 · Answer 1 · answered Nov 10 '20 at 13:36

Without knowing the version of SQL Server, I have assumed the OP is using a recent version. If not they will need to replace STRING_AGG with FOR XML PATH (there are plenty of examples on how to do this).

The method I use here is to use a Tally to create the groups, and then STRING_AGG to reaggregate the strings with a delimiter.

DECLARE @Chars tinyint = 2;

WITH N AS(
    SELECT N 
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (8000) --Use 4000 if nvarchar
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4),           
Groups AS(
    SELECT V.S,
           (T.I -1) / @Chars AS Grp
    FROM (VALUES('asdfjkhasbldfkasdfaklhgou'),
                ('b78345h39p7y907y'))V(S)
         JOIN Tally T ON T.I <= LEN(V.S)
    WHERE T.I % @Chars = 0
       OR T.I = LEN(V.S))
SELECT STRING_AGG(SUBSTRING(G.S,(G.grp*2)+1,2),'/') WITHIN GROUP (ORDER BY G.grp) AS NewS
FROM Groups G
GROUP BY G.S;

Though this could be done with a rCTE, they are inherently slow, and a Tally will be far faster; especially with large datasets.

SQL Server: Add slash every 2 characters

1 Answers1