求一条SQL语句

Question

select replace(translate('aa1122bb','0123456789','          '),' ') from dual;

Answer 1

9i:SQL> select * from t1;A
--------------------------------------------------------------------------------区杨柳青 西青道青云里11-4-502
区杨柳青青云里北小二楼3号楼SQL> select * from t1;A
--------------------------------------------------------------------------------区杨柳青 西青道青云里11-4-502
区杨柳青青云里北小二楼3号楼SQL> select trim(translate(a,trim(translate(a,'0123456789',' ')),' ')) from t1;TRIM(TRANSLATE(A,TRIM(TRANSLATE(A,'0123456789','')),''))
--------------------------------------------------------------------------------114502
38的版本的话，可以将trim用replace函数替换：
SQL> select replace(translate(a,replace(translate(a,'0123456789',' '),' ',''),'
'),' ','') from t1;REPLACE(TRANSLATE(A,REPLACE(TRANSLATE(A,'0123456789',''),'',''),''),'','')
--------------------------------------------------------------------------------114502
3

Answer 2

如果是oracle10g可以这样
select regexp_replace('区杨柳青 西青道青云里11-4-502','[^[:digit:]\-]','') from dual

Answer 3

SQL> select regexp_replace('区杨柳青 西青道青云里11-4-502','[^[:digit:]\-]','') from dual;REGEXP_R
--------
11-4-502

Answer 4

zmgowin(隐者(龙祖宗)) 的方法很不错！