oracle 去除英文字母,请教：如何去掉字段中的所有标点符号（包括全角的和半角的）...

TRANSLATE

Syntax

参见附图

Purpose

TRANSLATE 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

del.jpg

(10.31 KB, 下载次数: 49)

2005-10-14 13:15 上传