translate与REGEXP

TRANSLATE

Syntax

Purpose

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding(相应的) character in to_string. If expr is a character string, then you must enclose(把…放入) it in single quotation marks(引号). The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.

You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets(解释) the empty string as null, and if this function has a null argument, then it returns null.

TRANSLATE provides functionality related to that provided by the REPLACE function. REPLACE lets you substitute(替代) a single string for another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion(隐式转换).

Examples

把所有的非数字置空

CREATE OR REPLACE VIEW v_t AS SELECT 'x4943i1112xi3344a34o4m413i231312ng33' str FROM dual;
SELECT translate(str,'1234567890'||str,'1234567890') FROM v_t; -- 返回49431112334434441323131233
SELECT REGEXP_replace(str,'[^0-9]','') FROM v_t; -- 效果同上

把所有的数字置空

SELECT translate('x4943i1112xi3344a34o4m413i231312ng33','#1234567890','#') FROM dual; -- 返回xixiaoming
SELECT REGEXP_replace('x4943i1112xi3344a34o4m413i231312ng33','[1234567890]') FROM dual; -- 效果同上

从zhangsan,lisi,wangwu中取出第二个名字

SELECT regexp_substr(q'[zhangsan,lisi,wangwu]','[^,]+',1,2) NAME FROM dual; 

把名字Xi Xiao Ming改成首字母大写加.的格式，即X.X.M的格式

CREATE OR REPLACE VIEW v_t AS SELECT 'Xi Xiao Ming' str FROM dual;
SELECT translate(a1,' '||a2,'.') AS NAME FROM (SELECT str a1,LOWER(str) AS a2 FROM v_t); -- 返回X.X.M

查询a出现几次

SELECT regexp_count('axbccafbddscsaasadsebdcacbcc','a') FROM dual;