 |
Cut string in a Select
Hi, I try to create a procedure in oracle 9i. the procedure should
convert values alphabetic in numeric if they exist.
Values in the Str column may be either numeric or alphabetic
and the last characters will be numeric .
example
str 100
A100
B100
321
A321A
11
A11
I need to convert the values as follows.
100 --> 100
A100 --> 01100
B100 --> 02100
321 --> 321
A321 --> 01321
11 --> 11
A11A --> 0111
already found this resource but the alphabetic characters joins at the
back and I need then at the front.
Any ideas ty.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7...
select mat_codigo, decode(length(replace(translate(mat_codigo,'0123456789','0000000000'),'0',' '))
,NULL
, mat_codigo
, substr(mat_codigo,3,length(mat_codigo)-1) )
--from ma_con_matriculas
||
to_char( decode(length(replace(translate(mat_codigo,'0123456789','0000000000'),'0',' '))
, NULL
, ''
, ascii(substr(mat_codigo, 1))-64 ||
ascii(substr(mat_codigo, 2))-64)
) as mat_cod_ne
-- as mat_codigo_nuevo
-- , substr(mat_codigo, 3,length (mat_codigo_nuevo) )
from ma_con_matriculas
This is what i got so far but still with the same question, is it
possible to join the new values at the beging of the string???
TY.
A
-----------------------------------------------Reply-----------------------------------------------
"valigula" <valig@gmail.com> a crit dans le message de news: 1178813725.084929.107@n59g2000hsh.googlegroups.com...
| Hi,
|
| I try to create a procedure in oracle 9i. the procedure should
| convert values alphabetic in numeric if they exist.
|
| Values in the Str column may be either numeric or alphabetic
| and the last characters will be numeric .
|
| example
| str 100
| A100
| B100
| 321
| A321A
| 11
| A11
|
| I need to convert the values as follows.
| 100 --> 100
| A100 --> 01100
| B100 --> 02100
| 321 --> 321
| A321 --> 01321
| 11 --> 11
| A11A --> 0111
|
| already found this resource but the alphabetic characters joins at the
| back and I need then at the front.
|
| Any ideas ty.
|
| http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7...
|
select case
when substr(str,1,1) between 'A' abd 'Z' then to_char(ascii(substr(str,1,1))-ascii('A')+1,'00')||substr(str,2)
else str
end
from mytable
/
Regards
Michel Cadot
-----------------------------------------------Reply-----------------------------------------------
that is great thnks Michel ... One more what if there are two letters ... .... IA9903000395 VD9902700081 ... Should i do the case twice???
Ty
-----------------------------------------------Reply-----------------------------------------------
"valigula" <valig@gmail.com> a crit dans le message de news: 1178821439.305883.248@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
-----------------------------------------------Reply-----------------------------------------------
On 10 mayo, 22:21, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "valigula" <valig @gmail.com> a crit dans le message de news: 1178821439.305883.248 @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
On 10 mayo, 22:21, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "valigula" <valig @gmail.com> a crit dans le message de news: 1178821439.305883.248 @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
-----------------------------------------------Reply-----------------------------------------------
"valigula" <valig@gmail.com> a crit dans le message de news: 1178832426.865919.93@e51g2000hsg.googlegroups.com...
On 10 mayo, 22:21, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "valigula" <valig @gmail.com> a crit dans le message de news: 1178821439.305883.248 @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
-----------------------------------------------------
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
-----------------------------------------------Reply-----------------------------------------------
Sorry if it seems confuse. All the cases are about 500k so i wont be possible to post.
The reason i was posting bit a bit, is because i was trying to fully
understand what i was doing.
I can continue from here. I eally aprecciate your help. Thanks again
|
 |
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
