On 10 mayo, 22:21, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "valigula" <valig..._at_gmail.com> a écrit dans le message de news: 1178821439.305883.248..._at_w5g2000hsg.googlegroups.com...
> | that is great thnks Michel ...
> | One more what if there are two letters ...
> | ....
> | IA9903000395
> | VD9902700081
> | ...
> |
> | Should i do the case twice???
> |
> | Ty
> |
>
> SQL> with
> 2 data as (
> 3 select str,
> 4 instr(translate(str,'0123456789','//////////'),'/')-1 lg,
> 5 substr(str,1,instr(translate(str,'0123456789','//////////'),'/')-1) pref,
> 6 substr(str,instr(translate(str,'0123456789','//////////'),'/')) suf
> 7 from t
> 8 )
> 9 select str,
> 10 to_char(to_number(utl_raw.cast_to_raw(pref))
> 11 -to_number(utl_raw.cast_to_raw(rpad('@',lg,'@'))),
> 12 rpad('fm00',2*(lg+1),'00'))
> 13 || suf str
> 14 from data
> 15 /
> STR STR
> -------------------- --------------------
> 100 100
> A100 01100
> B100 02100
> IA9903000395 09019903000395
> VD9902700081 16049902700081
>
> 5 rows selected.
>
> Good luck to decypher. :)
>
> Regards
> Michel Cadot
Received on Thu May 10 2007 - 16:22:29 CDT
