Is this satisfied your requirements?
CREATE FUNCTION three_digit_numbers(in_s VARCHAR(50) )
RETURNS VARCHAR(100)
LANGUAGE SQL
READS SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
WITH add_leading_zero
(ordinal, index, result) AS (
VALUES (0, 0, CAST(in_s AS VARCHAR(100)) )
/* */ UNION ALL /* */
SELECT pre.ordinal + 1
, new.index
, INSERT( pre.result
, pre.ordinal*4 + 1
, 0
, SUBSTR('00', 1, 4 - new.index + pre.index) )
FROM add_leading_zero pre
, (SELECT index
, INT(ROWNUMBER() OVER(ORDER BY index) ) ordinal
FROM (SELECT 1+n1+10*n2
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)
WHERE n1+10*n2 <= LENGTH(in_s)
) N(index)
WHERE SUBSTR(in_s||'.',index,1) = '.'
) new
WHERE pre.ordinal < 1000
AND new.ordinal = pre.ordinal + 1
)
SELECT result
FROM add_leading_zero
WHERE ordinal
= (SELECT MAX(ordinal)
FROM add_leading_zero
)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered
------------------------------
WITH
T3 (C1) AS (
VALUES ('1'),
('1.1'),
('1.2.1.2'),
('1.10.1'),
('1.10.1.2.2'),
('1.22.99.1'),
('1.2'),
('2'),
('2.7.7'),
('2.8.11'),
('1.4.4'),
('1.3.3.7.4'),
('1.3.2.7'),
('1.4.1')
)
SELECT c1
FROM T3
WHERE SUBSTR(c1,1,1) IN ('1', '2')
ORDER BY
three_digit_numbers(c1);
------------------------------------------------------------------------------
C1
----------
1
1.1
1.2
1.2.1.2
1.3.2.7
1.3.3.7.4
1.4.1
1.4.4
1.10.1
1.10.1.2.2
1.22.99.1
2
2.7.7
2.8.11
14 record(s) selected.
|
