How to add leading zeroes

**Lennart** · Jun 27 '08, 07:20 PM

Re: How to add leading zeroes

On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

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.SYSDUMMY 1
UNION SELECT '.4.' , '.004.'
FROM SYSIBM.SYSDUMMY 1
UNION SELECT '.22.' , '.022.'
FROM SYSIBM.SYSDUMMY 1) 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.
>

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,inde x+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,inde x
+1,coalesce(nul lif(locate('+', substr(x.c,inde x+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

**Serman D.** · Jun 27 '08, 07:20 PM

Re: How to add leading zeroes

On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

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.

**Lennart** · Jun 27 '08, 07:20 PM

Re: How to add leading zeroes

On May 6, 1:48 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

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.

429

http://groups.google.com/groups?as_umsgid=22d2e427.0308172007.7eb298f@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--** · Jun 27 '08, 07:20 PM

Re: How to add leading zeroes

>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(sources tring,
(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.

How to add leading zeroes

How to add leading zeroes

Comment

Comment

Comment

Comment