布景:给同事做了个报表,使用时从EXCEL、网页等向报表中拷贝发芽前提(局编)。
, b$ K' c- g, U2 S3 o, @1 Y" U 用户反映查不出数据——发芽拜访后发现用户填的局编带有空格——随手加了TRIM# g2 O, I3 L; o2 J$ L% Z: W
又有查不出数据的情形——带有全角空格——用Replace替代全角空格$ X+ h3 T0 h) |
还有查不到,发芽拜访发现可能混有换行、EXCEL中神秘的空白符等。
' h @; u0 W" T& L3 o0 e0 X 措置:第一反映是让用户粘贴前先转贴到记事本中,“过滤”一下无效字符。简单、有用,但很不友好。. I* Z: p$ Z Q S+ t+ G/ }/ L* z
解决:咨询后得知合理的局编仅由数字组成,那么可以安心的滤失踪所有非数字字符。(注:10g往后用regex_replace很轻易实现。)5 J" Z+ D1 W" L5 S/ x0 E3 F: t+ C
对此Oracle供给了translate(a, b, c)函数:
: O$ X0 t6 K. ^. P! L9 q" A/ M a,b,c三个参数均是字符串当a,b,c中任一个为NULL时,返回NULL.否则,对a中每个字符x、在b中查找:如找不到,则追加x到返回值中,如找到,则再考试c中取对应位置的字符y(例如x呈此刻b中、是第6个字符,则从c中也取第六个字符,若c不足6个字符,则y为NULL)
* k+ U+ k( D5 X4 a 如y不为NULL,追加y到返回值中如y为NULL,忽略x非数字字符太多,不成能一一列出。是以分两步:首先滤出非数字字符f:
' o, ^8 A7 L O- R- G9 L8 n view plain select translate('a1e2c3z', '#0123456789', '#') from dual;然后将f作为过滤串再滤一次,即可获得纯数字串(拼接一个0前缀保证非空,避免返回NULL),组合起来:, y: D: `; `9 o; u/ P
view plain select translate( 'a1e2c3z', '0' || translate('a1e2c3z', '#0123456789', '#'), '0') from dual; |