February 8, 2018 at 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!
February 8, 2018 at 9:54 am
DataAnalyst011 - Thursday, February 8, 2018 9:28 AMHi 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?
😎
February 8, 2018 at 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
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".
February 8, 2018 at 11:30 am
Eirikur Eiriksson - Thursday, February 8, 2018 9:54 AMDataAnalyst011 - Thursday, February 8, 2018 9:28 AMHi 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.
February 8, 2018 at 11:31 am
ScottPletcher - 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.MyTable
Thanks a bunch for the work you put into this. I'm going to give this a try.
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) "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".
February 8, 2018 at 12:54 pm
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
February 8, 2018 at 1:41 pm
replace(cast(FORMAT(cast(Column1 as float), '### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ### | ###') as varchar(max)),' | 000', '')
February 8, 2018 at 1:44 pm
Your solutions seem very clever, but I wouldn't test my luck like that when having a varchar(max) column.
February 8, 2018 at 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
February 8, 2018 at 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.
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".
February 8, 2018 at 2:07 pm
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.
February 8, 2018 at 2:13 pm
ScottPletcher - Thursday, February 8, 2018 2:02 PMFloat 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.
February 8, 2018 at 2:37 pm
Nevermind... this was dumb 🙂
_________________________
We're on 2016 right?
Using the sample table.SELECT REPLACE(FORMAT(CAST(column1 AS FLOAT),'N0'),',','|') FROM dbo.MyTable
February 9, 2018 at 1:37 am
Lynn Pettis - Thursday, February 8, 2018 2:02 PMOf 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
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