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