Split 60 character string every third character with pipes

• DataAnalyst011

  SSCarpal Tunnel

  Points: 4035

  More actions

  February 8, 2018 at 9:28 am

  #336086

  Hi All,

  I'm needing to split a 60 character string every third character with spaces/pipes and filter out any set that is '000'. For example, this:

  '158001006000000000000000000000000000000000000000000000000000'

  would become this...

  158 | 001 | 006

  Here is some sample table (not my design!)

  CREATE TABLE MyTable (Column1 VARCHAR(MAX) SPARSE)

  INSERT INTO MyTable VALUES
    ('158001258006000000000000000000000000000000000000000000000000'),
    ('158001006000000000000000000000000000000000000000000000000000'),
    ('158267001118365397398399006000000000000000000000000000000000'),
    ('112070001365006000000000000000000000000000000000000000000000')

  Any help is GREATLY appreciated!

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  February 8, 2018 at 9:54 am

  #1979057

  DataAnalyst011 - Thursday, February 8, 2018 9:28 AM

  Hi All,

  I'm needing to split a 60 character string every third character with spaces/pipes and filter out any set that is '000'. For example, this:

  '158001006000000000000000000000000000000000000000000000000000'

  would become this...

  158 | 001 | 006

  Here is some sample table (not my design!)

  CREATE TABLE MyTable (Column1 VARCHAR(MAX) SPARSE)

  INSERT INTO MyTable VALUES
    ('158001258006000000000000000000000000000000000000000000000000'),
    ('158001006000000000000000000000000000000000000000000000000000'),
    ('158267001118365397398399006000000000000000000000000000000000'),
    ('112070001365006000000000000000000000000000000000000000000000')

  Any help is GREATLY appreciated!

  This is trivial, what is the purpose?
  😎

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  February 8, 2018 at 10:39 am

  #1979067

  I 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.MyTable

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• DataAnalyst011

  SSCarpal Tunnel

  Points: 4035

  More actions

  February 8, 2018 at 11:30 am

  #1979080

  Eirikur Eiriksson - Thursday, February 8, 2018 9:54 AM

  DataAnalyst011 - Thursday, February 8, 2018 9:28 AM

  Hi All,

  I'm needing to split a 60 character string every third character with spaces/pipes and filter out any set that is '000'. For example, this:

  '158001006000000000000000000000000000000000000000000000000000'

  would become this...

  158 | 001 | 006

  Here is some sample table (not my design!)

  CREATE TABLE MyTable (Column1 VARCHAR(MAX) SPARSE)

  INSERT INTO MyTable VALUES
    ('158001258006000000000000000000000000000000000000000000000000'),
    ('158001006000000000000000000000000000000000000000000000000000'),
    ('158267001118365397398399006000000000000000000000000000000000'),
    ('112070001365006000000000000000000000000000000000000000000000')

  Any help is GREATLY appreciated!

  This is trivial, what is the purpose?
  😎

  A just question! Each set of three constitutes a code that our business users understand. They want it piped out and filtered for readability.

• DataAnalyst011

  SSCarpal Tunnel

  Points: 4035

  More actions

  February 8, 2018 at 11:31 am

  #1979081

  ScottPletcher - Thursday, February 8, 2018 10:39 AM

  I 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.MyTable

  Thanks a bunch for the work you put into this. I'm going to give this a try.

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  February 8, 2018 at 11:44 am

  #1979082

  DataAnalyst011 - Thursday, February 8, 2018 11:31 AM

  ScottPletcher - Thursday, February 8, 2018 10:39 AM

  I 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.MyTable

  Thanks 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.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 8, 2018 at 12:54 pm

  #1979097

  ScottPletcher - Thursday, February 8, 2018 11:44 AM

  [
  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.]

  I also used a specialized tally table (starting with 0 and steps of 3) and came up with a simpler approach.  (I thought that using a 0-base was more obvious than using a 1-base when incrementing by three, so I used that even though it made the formulas a little more complicated.)

  
SELECT Column1, STUFF(ss,1, 1, '')
FROM MyTable mt
CROSS APPLY
(
    SELECT '|' + SUBSTRING(mt.Column1, n + 1, 3)
    FROM ( VALUES(0), (3), (6), (9), (12), (15), (18), (21), (24) ) Tally(n)
    WHERE SUBSTRING(mt.Column1, n + 1, 3) > '000'
    ORDER BY n
    FOR XML PATH('')
) v(ss)


  Drew

  Edit: I didn't feel like entering the entire tally table, so you'll need to fill it out to 57.

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Bert-701015

  SSCommitted

  Points: 1615

  More actions

  February 8, 2018 at 1:41 pm

  #1979103

  replace(cast(FORMAT(cast(Column1 as float), '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(max)),' | 000', '')

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  February 8, 2018 at 1:44 pm

  #1979106

  Your solutions seem very clever, but I wouldn't test my luck like that when having a varchar(max) column.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 8, 2018 at 2:02 pm

  #1979108

  Of course you could also do this (will need some changes if you have null columns, but those could be excluded in the query):
  
CREATE TABLE MyTable (Column1 VARCHAR(MAX) SPARSE);

  INSERT INTO MyTable VALUES
      ('158001258006000000000000000000000000000000000000000000000000'),
      ('158001006000000000000000000000000000000000000000000000000000'),
      ('158267001118365397398399006000000000000000000000000000000000'),
      ('112070001365006000000000000000000000000000000000000000000000');
  GO

  WITH etally(n) AS (SELECT ROW_NUMBER()OVER (ORDER BY (SELECT NULL)) - 1 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0))dt1(n)),
       Pos(n) AS (SELECT 1 + (n * 3) FROM eTally WHERE 1 + (n * 3) <= 60) -- SELECT * FROM Pos
  SELECT
    [mt].[Column1]
    ,[ca1].[FormatColumn1]
  FROM
    [dbo].[MyTable] [mt]
    CROSS APPLY (SELECT STUFF((SELECT '|' + SUBSTRING([mt].[Column1],p.n,3)
                              FROM Pos p
                              ORDER BY p.n
                              FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,''))ca1(FormatColumn1);
  GO
  DROP TABLE [dbo].[MyTable];
  GO
  

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  February 8, 2018 at 2:02 pm

  #1979109

  Float doesn't have nearly 60 digits of precision, does it?

  SELECT REPLACE(CAST(FORMAT(CAST('123456789012345678901234567890123456789012345678901234567890' AS float),
  '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(8000)), ' | 000', '')

  As I stated originally, I'd keep this simple: no XML, no direct tally table, no variables / changing values.  If they're not needed, they'll just cloud things up.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• DataAnalyst011

  SSCarpal Tunnel

  Points: 4035

  More actions

  February 8, 2018 at 2:07 pm

  #1979110

  Haha that's what I love about this place - loads of different ways to do the same thing. I really appreciate all of this input. It really helps with my needed solution and learning in general. Thanks again.

• Bert-701015

  SSCommitted

  Points: 1615

  More actions

  February 8, 2018 at 2:13 pm

  #1979113

  ScottPletcher - Thursday, February 8, 2018 2:02 PM

  Float doesn't have nearly 60 digits of precision, does it?

  SELECT REPLACE(CAST(FORMAT(CAST('123456789012345678901234567890123456789012345678901234567890' AS float),
  '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(8000)), ' | 000', '')

  As I stated originally, I'd keep this simple: no XML, no direct tally table, no variables / changing values.  If they're not needed, they'll just cloud things up.

  you are correct.

• SQLPirate

  SSCrazy

  Points: 2880

  More actions

  February 8, 2018 at 2:37 pm

  #1979116

  Nevermind... this was dumb 🙂

  _________________________
  We're on 2016 right?

  Using the sample table.
  SELECT REPLACE(FORMAT(CAST(column1 AS FLOAT),'N0'),',','|') FROM dbo.MyTable

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  February 9, 2018 at 1:37 am

  #1979188

  Lynn Pettis - Thursday, February 8, 2018 2:02 PM

  Of course you could also do this (will need some changes if you have null columns, but those could be excluded in the query):
  
CREATE TABLE MyTable (Column1 VARCHAR(MAX) SPARSE);

  INSERT INTO MyTable VALUES
      ('158001258006000000000000000000000000000000000000000000000000'),
      ('158001006000000000000000000000000000000000000000000000000000'),
      ('158267001118365397398399006000000000000000000000000000000000'),
      ('112070001365006000000000000000000000000000000000000000000000');
  GO

  WITH etally(n) AS (SELECT ROW_NUMBER()OVER (ORDER BY (SELECT NULL)) - 1 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt(n) CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0))dt1(n)),
       Pos(n) AS (SELECT 1 + (n * 3) FROM eTally WHERE 1 + (n * 3) <= 60) -- SELECT * FROM Pos
  SELECT
    [mt].[Column1]
    ,[ca1].[FormatColumn1]
  FROM
    [dbo].[MyTable] [mt]
    CROSS APPLY (SELECT STUFF((SELECT '|' + SUBSTRING([mt].[Column1],p.n,3)
                              FROM Pos p
                              ORDER BY p.n
                              FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,''))ca1(FormatColumn1);
  GO
  DROP TABLE [dbo].[MyTable];
  GO
  

  Change the last line by adding the text() function, many times faster that way as it bypasses the reconstruction of the XML output set.
  😎
  
FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,''))ca1(FormatColumn1);