Re: Cut string in a Select

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 10 May 2007 22:21:14 +0200
Message-ID: <46437eba$0$990$426a74cc@news.free.fr>

"valigula" <valigula_at_gmail.com> a écrit dans le message de news: 1178821439.305883.248250_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 - 15:21:14 CDT