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"
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, '')
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?
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;', '&')