| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cut string in a Select
"valigula" <valigula_at_gmail.com> a écrit dans le message de news: 1178832426.865919.93360_at_e51g2000hsg.googlegroups.com...
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
that is great .... I was doing with this ... select mat_codigo,
decode(length(replace(translate(mat_codigo,'0123456789','0000000000'),'0',''))
,NULL
, mat_codigo
,
to_char( decode(length(replace(translate(mat_codigo,'0123456789','0000000000'),'0',''))
, NULL
, ''
, ascii(substr(mat_codigo, 1,1))-64
|| ascii(substr(mat_codigo, 2,2))-64 )
|| substr(mat_codigo,3,length(mat_codigo)) )
)
from ma_con_matriculas
how do i eliminate the extras '0' ... ?? i dont need any format ...
actually i only need the last digit
11= 1 21= 1 1 = 1
I don't understand.
Please post a definitive specification and don't change it each time.
Also post a full test case: input -> output for all cases you can have.
Regards
Michel
Received on Thu May 10 2007 - 23:34:09 CDT
 |
 |