SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split 60 character string every third character with pipes


Split 60 character string every third character with pipes

Author
Message
DataAnalyst011
DataAnalyst011
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 548
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
Eirikur Eiriksson
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125423 Visits: 21783
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?
Cool

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64244 Visits: 8724
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
DataAnalyst011
DataAnalyst011
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 548
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?
Cool

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
DataAnalyst011
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 548
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
ScottPletcher
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64244 Visits: 8724
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
drew.allen
drew.allen
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49339 Visits: 14720
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Bert-701015
Bert-701015
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 1001
replace(cast(FORMAT(cast(Column1 as float), '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(max)),' | 000', '')

Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)

Group: General Forum Members
Points: 127470 Visits: 21917
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
Lynn Pettis
SSC Guru
SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)SSC Guru (302K reputation)

Group: General Forum Members
Points: 302942 Visits: 41507

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



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search