By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,175 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,175 IT Pros & Developers. It's quick & easy.

How to add leading zeroes

P: n/a
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....m-db2/200805/1

Jun 27 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrote:
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.
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
Jun 27 '08 #2

P: n/a
On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@uwewrote:
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.
Jun 27 '08 #3

P: n/a
On May 6, 1:48 pm, "lenygold via DBMonster.com" <u41482@uwewrote:
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/gr*********...ing.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

Jun 27 '08 #4

P: n/a
>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.
Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.