How to add leading zeroes

[lenygold via DBMonster.com]

I have the following column:

1.4.1
1.10.1
1.10.1.2.2
1.22.99.1
2
2.8.11
2.7.7

I am trying to add leading zeros to every part before dots, to make them 3
digit numbers:
001.004.001
001.010.001
001.010.001
001.022.099.001
002
002.008.011
002.007.007

My first try did't work:
SELECT
REPLACE ('1.4.22.34 ', S, B)
FROM (SELECT '.34 ' , '.034 '
FROM SYSIBM.SYSDUMMY1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY1) T(S,B)

output:

1.4.022.34
1.4.22.034
1.004.22.34

Any idea how to produce requested result?
Thank's in advance
Lenny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1

[Lennart]

You can do it with a case expression:

case when length(rtrim(x)) = 1 then '00' || x ...

but you are (IMO) going in the wrong direction. Let's look at a
simplified example:

[lelle@53dbd181 ~]$ db2 "select * from lateral(values('1+21+131'))
x(c), table(elements(x.c))"

C ORDINAL INDEX
-------- ----------- -----------
1+21+131 0 0
1+21+131 1 2
1+21+131 2 5

3 record(s) selected.

index+1 will give us the startpos for each new number:

[lelle@53dbd181 ~]$ db2 "select substr(x.c,index+1) from
lateral(values('1+21+131')) x(c), table(elements(x.c))"
1
--------
1+21+131
21+131
131

The stop pos for each number will be the next '+' and in case there
are no '+' left, we can use the length of the remaining string:

[lelle@53dbd181 ~]$ db2 "select substr(x.c,index
+1,coalesce(nullif(locate('+', substr(x.c,index+1)),
0)-1,length(substr(x.c,index+1)))) from lateral(values('1+21+131'))
x(c), table(elements(x.c))"

1
--------
1
21
131

IMO, this kind of problem is better solved in the application layer

/Lennart

[Tonkuma]

> 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.

[Serman D.]

On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwe> wrote:

> Any idea how to produce requested result?

I would have used perl:

$ cat /tmp/foo.pl
use strict;
use warnings;

while(my $line = <DATA>){
print join(q{.}, map { sprintf("%03d", $_); } split(/\./,
$line));
}

__DATA__

1.4.1
1.10.1
1.10.1.2.2
1.22.99.1
2
2.8.11
2.7.7

$ perl -wl /tmp/foo.pl
001.004.001
001.010.001
001.010.001.002.002
001.022.099.001
002
002.008.011
002.007.007

--
Serman D.

[lenygold via DBMonster.com]

Thank' s everybody for help.
The reason why i need this conversion is to sort the following rows:

Here is my solution using nested tree model:

12. GENERIC SOLUTION USING NESTED TREE APROACH TO SORT UNSOrTABLE

1(1,22)
2 (23,28)

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.7.7 2.8.11
(2,3)(4,5) (6,7) (8,9) (10,11) (12,13) (14,15) (16,17) (18,19)
(20,21) (24,25) (26,27)

WITH T1 (L_NUM,R_NUM) AS
(VALUES(1,22),
(2,3),
(6,7),
(16,17),
(18,19),
(20,21),
(4,5),
(23,28),
(24,25),
(26,27),
(14,15),
(10,11),
(8,9),
(12,13)),
T2(RN,L_NUM,R_NUM) AS
(SELECT ROW_NUMBER() OVER(),L_NUM,R_NUM FROM T1),
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')),
T4(RN,C1) AS
(SELECT ROW_NUMBER() OVER(),C1 FROM T3),
T5(L_NUM,R_NUM,C1) AS
(SELECT L_NUM,R_NUM,C1 FROM T2,T4
WHERE T2.RN = T4.RN)
SELECT F1.C1
FROM T5 AS F1, T5 AS F2
WHERE F1.L_NUM BETWEEN F2.L_NUM AND F2.R_NUM
AND F2.C1 IN('1','2')
ORDER BY F1.L_NUM;

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

--

[Tonkuma]

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.

[Lennart]

On May 6, 1:48 pm, "lenygold via DBMonster.com" <u41482@uwe> wrote:
> Thank' s everybody for help.
> The reason why i need this conversion is to sort the following rows:
>

I didn't realize that it was sorting that you where after. The
following post by Vadim is an alternative way of attacking traversal
of a tree, and therefor might be of interest to you.

http://groups.google.com/groups?as_umsgid=22d2e427.0308...@posting.google.com

A total ordering is defined as:

6. For any nodes A and B we write A > B whenever
i. B is Ancestor of A or
ii. there exists node B' which is an ancestor of B,
and A' which is an ancestor of A,
and both A' and B' having the same parent,
and A' > B'

/Lennart

[--CELKO--]

>> Although I agree with Lennart, I want to try in SQL simply for my entertainment. <<

I agree, but it is fun. Life is easier if the input string always ends
with a period, and I think that is required in one of the ISO or US
Government Standards for outlines.

1) Can you nest function calls 100 levels deep? So we just nest
REPLACE() calls that deep:

SELECT REPLACE ( ..
REPLACE (sourcestring, '1.', '001.'),
..
'99.', '099.')
FROM Foobar;

Since the functions will be on the stack, this ought to run pretty
fast. But only a LISP programmer would love it.

2) Go back to procedural programming and write a loop?

CREATE TABLE LeadingZeros
(instring VARCHAR(3) NOT NULL PRIMARY KEY,
outstring CHAR(4) NOT NULL
CHECK (outstring SIMILAR TO '[:DIGITS:][:DIGITS:][:DIGITS:]\.');

INSERT INTO LeadingZeros (convert_nbr, instring, outstring)
VALUES (1, '1.', '001.'), (2, '2.', '002.'), .., (99, '99.', '099.');

CREATE PROCEDURE PaddingZeros ()

LANGUAGE SQL
READS SQL DATA

BEGIN DECLARE i INTEGER;
SET i = 1;
WHILE i < 100
DO UPDATE Foobar
SET sourcestring
= REPLACE(sourcestring,
(SELECT instring
FROM LeadingZeros
WHERE i = convert_nbr),
(SELECT outstring
FROM LeadingZeros
WHERE i = convert_nbr));
SET i = i+1;
END WHILE;
END;

This can be done with a recursive CTE, but that might be even worse.

[lenygold via DBMonster.com]

Thnk You Tonkuma very much, your SQL AND UDF are working perfect
Leny G.

--
Message posted via http://www.dbmonster.com