Oracle PL/SQL 过滤非数字字符

背景：给同事做了个报表，使用时从EXCEL、网页等向报表中拷贝查询条件（局编）。

用户反映查不出数据——调查后发现用户填的局编带有空格——随手加了TRIM

又有查不出数据的情况——带有全角空格——用Replace替换全角空格

还有查不到，调查发现可能混有换行、EXCEL中神秘的空白符等。

处理：第一反应是让用户粘贴前先转贴到记事本中，“过滤”一下无效字符。简单、有效，但很不友好。

解决：咨询后得知合理的局编仅由数字组成，那么可以放心的滤掉所有非数字字符。（注：10g以后用regex_replace很容易实现。）

对此Oracle提供了translate(a, b, c)函数：

• a,b,c三个参数均是字符串
• 当a,b,c中任一个为NULL时，返回NULL。
• 否则，对a中每个字符x、在b中查找：
  如找不到，则追加x到返回值中，
  如找到，则再尝试从c中取对应位置的字符y（例如x出现在b中、是第6个字符，则从c中也取第六个字符，若c不足6个字符，则y为NULL）
  如y不为NULL，追加y到返回值中
  如y为NULL，忽略x

非数字字符太多，不可能一一列出。因此分两步：首先滤出非数字字符f：

select translate('a1e2c3z', '#0123456789', '#') from dual;

然后将f作为过滤串再滤一次，即可得到纯数字串（拼接一个0前缀保证非空，避免返回NULL），组合起来：

select translate( 'a1e2c3z', '0' || translate('a1e2c3z', '#0123456789', '#'), '0') from dual;