Re: Cut string in a Select

"valigula" <valigula_at_gmail.com> a écrit dans le message de news: 1178813725.084929.107930_at_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:7116760760159
|

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 Received on Thu May 10 2007 - 12:50:16 CDT