（007）每日SQL学习：将字符和数字分离

with aa as
(
select 'sad10' as data from dual
union all
select  'datf20' as data from dual
union all
select 'java30' as data from dual
)
select regexp_replace(data,'[0-9]','') as english,
　　　 regexp_replace(data,'[^0-9]','') as num 
from aa
     ENGLISH    NUM
1    sad    10
2    datf    20
3    java    30

说明：regexp_replace(data,'[0-9]','')就是多次的把数字替换成了空；[0-9]表示：[0123456789]；

regexp_replace(data,'[^0-9]')就是多次的把非数字替换成了空，[^0-9]中的^表示否。

如果对正则表达式不是很熟悉还可以用另外一种方法:

translate函数

with aa as
(
select 'sad10' as data from dual
union all
select  'datf20' as data from dual
union all
select 'java30' as data from dual
)
select translate(data,'a0123456789','a') as english,
　　　 translate(data,'0123456789'||data,'0123456789') as num 
from aa
     ENGLISH    NUM
1    sad    10
2    datf    20
3    java    30

 

转载于:https://www.cnblogs.com/littlewu/p/8257479.html