Insert Characters into a String

Transact-SQL
1

Is there a way to concatenate a character every nth character in a string in a select statement? So for instance if I have the following string:

"sdlofikjoweij3o4t890rjvolddkljfg"

And I want to put an underscore character every 5 characters I want to output the following:

"sdlof_ikjow_eij3o_4t890_rjvol_ddklj_fg"

2

Fairly easy in SQL Server if you have a "standard" tally table. Sadly I can't include an inline tally table in my post because a stupd filter at work prevents it, but you can create the "tally" table yourself. It's just a table of sequential integers from 0 to 1,000,000 (as most people use, but 100,000 or even 10,000 might be enough for your needs).

CREATE TABLE dbo.tally (
number int NOT NULL,
CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( number ) WITH ( FILLFACTOR = 100 )
)
INSERT INTO dbo.tally VALUES(0);
--INSERT other numbers into the tally here, from 1 to whatever

SELECT STUFF((
    SELECT '_' + SUBSTRING(string, (t.number - 1) * 5 + 1, 5)
    FROM (
        SELECT 'sdlofikjoweij3o4t890rjvolddkljfg' AS string
    ) AS test_data
    INNER JOIN dbo.tally t ON t.number BETWEEN 1 AND CEILING(LEN(string) / 5.0)
    ORDER BY t.number
    FOR XML PATH('')
), 1, 1, '')
3

Thanks, that almost does the trick. I think because of the XML conversion it is taking symbols like > and turning it into >, is there a way around that?

4

Yeah; easiest is to just brute-force the chars back into the final string. Note: Remove the space between & and l/g/a in the REPLACE strings.

SELECT REPLACE(REPLACE(REPLACE(STUFF((
SELECT '_' + SUBSTRING(string, (t.number - 1) * 5 + 1, 5)
FROM (
SELECT 'sdlofikjoweij3o4t890rjvolddkljfg' AS string
) AS test_data
INNER JOIN dbo.tally t ON t.number BETWEEN 1 AND CEILING(LEN(string) / 5.0)
ORDER BY t.number
FOR XML PATH('')
), 1, 1, ''), '& lt;', '<'), '& gt;', '>'), '& amp;', '&')