Re: Cut string in a Select

From: valigula <valigula_at_gmail.com>
Date: 10 May 2007 14:27:06 -0700
Message-ID: <1178832426.865919.93360@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
.....
IA	901
VD	2204
VD	2204

Received on Thu May 10 2007 - 16:27:06 CDT