Split 60 character string every third character with pipes
-
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!
- DataAnalyst011 - Thursday, February 8, 2018 9:28 AM
This is trivial, what is the purpose?
-
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.MyTableSQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- Eirikur Eiriksson - Thursday, February 8, 2018 9:54 AMDataAnalyst011 - Thursday, February 8, 2018 9:28 AM
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.
- ScottPletcher - Thursday, February 8, 2018 10:39 AM
Thanks a bunch for the work you put into this. I'm going to give this a try.
- DataAnalyst011 - Thursday, February 8, 2018 11:31 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.]SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Thursday, February 8, 2018 11:44 AM
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 -
replace(cast(FORMAT(cast(Column1 as float), '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(max)),' | 000', '') -
Your solutions seem very clever, but I wouldn't test my luck like that when having a varchar(max) column.
-
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');
GOWITH 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 -
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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.
- ScottPletcher - Thursday, February 8, 2018 2:02 PM
you are correct.
-
Nevermind... this was dumb
_________________________
We're on 2016 right?Using the sample table.
SELECT REPLACE(FORMAT(CAST(column1 AS FLOAT),'N0'),',','|') FROM dbo.MyTable - Lynn Pettis - Thursday, February 8, 2018 2:02 PM
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);
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply