> IMO, this kind of problem is better solved in the application layer
>
> /Lennart
Although I agree with Lennart, I want to try in SQL simply for my
entertainment.
------------------------------ Commands Entered
------------------------------
WITH Test_data(str) AS (
VALUES
('1.4.1')
,('1.10.1')
,('1.10.1.2.2')
,('1.22.99.1')
,('2')
,('2.8.11')
,('2.7.7')
)
,pos_dot AS (
SELECT str, index
, INT(ROWNUMBER() OVER(PARTITION BY str
ORDER BY index) ) ordinal
FROM Test_data
, LATERAL(SELECT 1+n1+10*n2+100*n3
FROM (VALUES 0,1,2,3,4,5,6,7,8,9) N(n1)
, (VALUES 0,1,2,3,4,5,6,7,8,9) N(n2)
, (VALUES 0,1,2,3,4,5,6,7,8,9) N(n3)
WHERE n1+10*n2+100*n3 <= LENGTH(str)
) N(index)
WHERE SUBSTR(str||'.',index,1) = '.'
)
,add_leading_zero
(ordinal, index, str, result, str_order) AS (
SELECT 0, 0, str, CAST(str AS VARCHAR(50) )
, ROWNUMBER() OVER()
FROM Test_data
/* */ UNION ALL /* */
SELECT pre.ordinal + 1
, new.index
, pre.str
, INSERT( pre.result
, pre.ordinal*4 + 1
, 0
, SUBSTR('00', 1, 4 - new.index + pre.index) )
, pre.str_order
FROM add_leading_zero pre
, pos_dot new
WHERE pre.ordinal < 4000
AND new.str = pre.str
AND new.ordinal = pre.ordinal + 1
)
SELECT str AS "source string"
, result AS "3 digit numbers"
FROM add_leading_zero T1
WHERE ordinal
= (SELECT MAX(ordinal)
FROM pos_dot T2
WHERE T2.str = T1.str
)
ORDER BY str_order
;
------------------------------------------------------------------------------
source string 3 digit numbers
------------- --------------------------------------------------
1.4.1 001.004.001
1.10.1 001.010.001
1.10.1.2.2 001.010.001.002.002
1.22.99.1 001.022.099.001
2 002
2.8.11 002.008.011
2.7.7 002.007.007
7 record(s) selected.
|
