How to seperate character data from numeric data
January 30th, 2006 By Eddie Awad
You have an alphanumeric column in your database and you want to split this column into two, one column containing the character data only, and a second column containing the numeric data only. For example, you want ‘hello5623′ to be ‘hello’ and ‘5623′.
This can be done in SQL using the two functions TRANSLATE and REPLACE:
HR@XE> select
2 replace
3 (
4 translate(data,'0123456789','0000000000'),'0'
5 ) myString,
6 to_number
7 (
8 replace
9 (
10 translate
11 (
12 lower(data),
13 'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')
14 ),'z'
15 )
16 ) myNumber
17 from (
18 select 'hello5623' data
19 from dual
20 )
21 /
MYSTR MYNUMBER
----- ----------
hello 5623
To get the number portion of the string, the trick is to use TRANSLATE to transform all characters to one character (z in this example) and then remove it using REPLACE.
To get the character portion of the string, the trick is to use TRANSLATE to transform all numbers to one number (0 in this example) and then remove it using REPLACE.
January 30th, 2006 at 7:45 am
I think it is better to use
select replace(translate(data,
replace(translate(data,’0123456789′,’0000000000′),’0′,null),rpad(’z',length(data),’z')),’z',null)
from
(select ‘hello5623′ data from dual)
to extract the numeric data
Best regards,
Claudiu Ariton
January 31st, 2006 at 2:59 pm
Regular expressions in 10G make this a whole lot simpler :
select orig,
regexp_replace(orig,’[0-9]‘,”,1
regexp_replace(orig,’[a-zA-Z]‘,’
from (select ‘helloe12te3st’ orig from dual);
/
ORIG
————-
REGEXP_REPLACE(ORIG,’[0-9]‘,”,1,0,’NM’)
——————————————–
REGEXP_REPLACE(ORIG,’[A-ZA-Z]‘,”,1,0,’NM’)
——————————————–
helloe12te3st
helloetest
123