Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Split 60 character string every third character with pipes RE: Split 60 character string every third character with pipes
February 8, 2018 at 11:44 am
DataAnalyst011 - Thursday, February 8, 2018 11:31 AMScottPletcher - Thursday, February 8, 2018 10:39 AMI say keep it simple unless you really need to do something more complex:
SELECT STUFF(
CASE WHEN SUBSTRING(column1, 1, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 1, 3) END +
CASE WHEN SUBSTRING(column1, 4, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 4, 3) END +
CASE WHEN SUBSTRING(column1, 7, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 7, 3) END +
CASE WHEN SUBSTRING(column1, 10, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 10, 3) END +
CASE WHEN SUBSTRING(column1, 13, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 13, 3) END +
CASE WHEN SUBSTRING(column1, 16, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 16, 3) END +
CASE WHEN SUBSTRING(column1, 19, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 19, 3) END +
CASE WHEN SUBSTRING(column1, 22, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 22, 3) END +
CASE WHEN SUBSTRING(column1, 25, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 25, 3) END +
CASE WHEN SUBSTRING(column1, 28, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 28, 3) END +
CASE WHEN SUBSTRING(column1, 31, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 31, 3) END +
CASE WHEN SUBSTRING(column1, 34, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 34, 3) END +
CASE WHEN SUBSTRING(column1, 37, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 37, 3) END +
CASE WHEN SUBSTRING(column1, 40, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 40, 3) END +
CASE WHEN SUBSTRING(column1, 43, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 43, 3) END +
CASE WHEN SUBSTRING(column1, 46, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 46, 3) END +
CASE WHEN SUBSTRING(column1, 49, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 49, 3) END +
CASE WHEN SUBSTRING(column1, 52, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 52, 3) END +
CASE WHEN SUBSTRING(column1, 55, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 55, 3) END +
CASE WHEN SUBSTRING(column1, 58, 3) = '000' THEN '' ELSE ' | ' + SUBSTRING(column1, 58, 3) END
, 1, 3, '') AS column1_trimmed
FROM dbo.MyTableThanks a bunch for the work you put into this. I'm going to give this a try.
[
You're welcome. [To be fair, I used a tally table to generate the CASE statements; nobody familiar with tally tables writes that kind of stuff by hand.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.