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

前些天, 在工作中遇到要从管道的名称中的解析出管道所在的路进行统计的问题. 系统中, 管道的名称基本都以"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 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值