On May 5, 5:27 pm, "lenygold via DBMonster.com" <u41482@[EMAIL PROTECTED]
> wrote:
> 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@[EMAIL PROTECTED]
~]$ 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@[EMAIL PROTECTED]
~]$ 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@[EMAIL PROTECTED]
~]$ 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
|
