从带各种符号的字段中把中文分离出来的方法

前些天, 在工作中遇到要从管道的名称中的解析出管道所在的路进行统计的问题. 系统中, 管道的名称基本都以"XX路-XX路"输入, "XX路"就是管道所在的道路名. 麻烦的是根据管道的结点, 名称里还加入了"XX路1-XX路1"或"XX路001#-XX路001#"之类的无关符号. 如果都要一个个进行解析, 那只能疯掉, 还好Oracle提供了一个批量的函数TRANSLATE. 这个函数就是把一个字符串中某些指定的字符用另一些字符来代替, 于是, 只要写成 TRANSLATE(管道名称, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ#_''"+-', ' ') 就能把名称中基本含有的无关的字符全变成了空格了, 而且如果字符是连续的, 那也会合成为一个空格, 真是美妙啊. 下面是TRANSLATE函数的英文原帮助:

TRANSLATE
Syntax
TRANSLATE(char, from, to)

Purpose
Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.


Example 1
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':



SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
FROM DUAL;

License
--------
9XXX999

Example 2
The following statement returns a license number with the characters removed and the digits remaining:



SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'0123456789')
"Translate example"
FROM DUAL;

Translate example
-----------------
2229