Hwere's a Head-Scratcher

realJSOP

Using SQL Server 2016 - I have a table with a TREE column (type is varchar) with data such as the following: 1.0 1.0.1 1.0.2 1.0.2.1 1.0.2.2 1.10.1.35 0) The number of "octets" (values between the periods) is an unknown quantity (it could be anywhere from 2 to 10 levels deep). 2) The number of digits within a given octet will be at least one, but never more than 2. Desired output: I want all single-digit octets to be 0-padded. So given the sample above, the output would look like this: 01.00 01.00.01 01.00.02 01.00.02.01 01.00.02.02 01.10.01.35 I did it like this, and after spending some time with a couple of DBA's they couldn't improve on it:

;WITH cte AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM MYTABLE
)
, cte2 AS
(
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('.'+TREE+'.',
'.0.', '.00.'),
'.1.', '.01.'),
'.2.', '.02.'),
'.3.', '.03.'),
'.4.', '.04.'),
'.5.', '.05.'),
'.6.', '.06.'),
'.7.', '.07.'),
'.8.', '.08.'),
'.9.', '.09.') AS TREE
FROM cte
)
SELECT * FROM cte2;

Is there a better way?

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Richard Deeming

Depends what you mean by "better". :) You're using SQL 2016, so STRING_AGG[^] is out - that was added in 2017. But you can use STRING_SPLIT[^], TRY_PARSE[^], and FORMAT[^]. And there are ways[^] to concatenate row values in 2016 and earlier. I've assumed a table without a primary key; if your table has one, use that instead of the generated ROW_NUMBER:

WITH cteRN As
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As RN,
tree
FROM
MyTable
)
SELECT
STUFF(
(
SELECT '.' + IsNull(Format(Try_Parse(P.value As int), 'D2'), P.value)
FROM cteRN As T2
CROSS APPLY string_split(T2.tree, '.') As P
WHERE T2.RN = T.RN
FOR XML PATH(''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '') As tree
FROM
cteRN As T
GROUP BY
T.RN
;

It's not pretty, but it works:

01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Mycroft Holmes · 01.00 01.00.01 01.00.02 01.00.02.01 01.00.02.02 01.10.01.35

:laugh: That is almost unreadable!

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

realJSOP

That's part of what makes it beautiful. :)

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Richard Deeming

I did say it wasn't pretty! :-D STRING_AGG would probably make it slightly better, but that needs SQL Server 2017. And if the source table has a primary key already, you can ditch the CTE.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

realJSOP

Wanna hear something funny? Our dev/test boxes are on SQL 2008R2, but our production and pre-production databases are 2016. I don't have access to the prod DBs, so I can't play with the code. :/

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

Richard Deeming

At least it's not the other way round. :-D You can play with it here: SQL Fiddle[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

realJSOP

That doesn't fix our in-house DB version conflicts. :) BTW, I misspoke - dev is 2012, test is 2008r2, and prod/pre-prod are 2016. So it's even worse than I initially indicated. I complained to the DBAs, and they just shrugged their shoulders...

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013