Split 60 character string every third character with pipes

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  February 9, 2018 at 1:58 am

  #1979189

  Just for fun, like a Christmas turkey, lots of STUFFing :hehe:

  
SELECT
REPLACE(
STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(
STUFF(Column1,58,0,'|')
,55,0,'|')
,52,0,'|')
,49,0,'|')
,46,0,'|')
,43,0,'|')
,40,0,'|')
,37,0,'|')
,34,0,'|')
,31,0,'|')
,28,0,'|')
,25,0,'|')
,22,0,'|')
,19,0,'|')
,16,0,'|')
,13,0,'|')
,10,0,'|')
,7,0,'|')
,4,0,'|'),
'|000','')
FROM MyTable


  Far away is close at hand in the images of elsewhere.
  Anon.

• sestell1

  SSChampion

  Points: 10230

  More actions

  February 9, 2018 at 8:09 am

  #1979242

  Lots of interesting solutions.
  I'd suggest though looking into doing this parsing in whatever is loading the data, so that it is set before it ever enters the database.  
  You may even want to think about storing each code as a separate record in a child table.  Then you could format it however you want when selecting and filtering on codes becomes much more efficient.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 9, 2018 at 8:47 am

  #1979257

  Eirikur Eiriksson - Friday, February 9, 2018 1:37 AM

  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);


  I will have to test this out.  I use this a lot in my code and if it helps, the better.