测试目的：
查询一个字符串中是否包括非数字以外的字符
查询一个字符串中某字符出现的频率
测试环境：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
实验过程：
1 查询一个字符串中是否包括非数字以外的字符
方法一：
SELECT TRANSLATE('111110000303030aaa000bbb', '0123456789', ' ') TRAN,
LENGTH(TRANSLATE('111110000303030aaa000bbb', '0123456789', ' ')) TRAN_LEN,
decode( TRIM ( TRANSLATE('111110000303030aaa000bbb', '0123456789', ' ')), NULL, 'Not Contain','Contain') TRAN_Trim_LEN
FROM DUAL;
SELECT TRANSLATE('111110000303030000', '0123456789', ' ') TRAN,
LENGTH(TRANSLATE('111110000303030000', '0123456789', ' ')) TRAN_LEN,
decode( TRIM ( TRANSLATE('111110000303030000', '0123456789', ' ')), NULL,'Not Contain' ,'Contain') TRAN_Trim_LEN
FROM DUAL;
注：translate中的to_string部分是一个空格，如果是Null的话，那么该转换的结果在任何情况均将为NULL
方法二：
SQL> SELECT 1
2 FROM DUAL
3 WHERE REGEXP_LIKE('a123', '[^[:digit:]]');
1
----------
1
SQL> ed
已写入 file afiedt.buf
1 SELECT 1
2 FROM DUAL
3* WHERE REGEXP_LIKE('123', '[^[:digit:]]')
SQL> /
未选定行
2 查询一个字符串中某字符出现的频率
方法一：
SELECT Length( 'xxxxa333aa' ) - Length( REPLACE('xxxxa333aa','a','') )
FROM dual;
方法二：
--如果在知道字符串包括哪些字符的前提下就可以使用如下方法
SELECT Length( translate( 'xxxxa333aa', 'ax3','a') )
FROM dual;
补充：
1 TRANSLATE
1）From_string和To_string单一字符一一对应，单From_string可以多于To_string的字符数
2）没有出现在From_string中的字符不会被替换；
3）对于1）中的From_string中的多余部分，会在返回值中被Remove掉；
4）to_string不能为Null
2 REPLACE
1）如果Replace_String（相当于Translate中的to_string）为NULL，则Search_string部分被移除；
2）如果Search_string部分为Null，则原字符串返回（实验不通过，参数个数无效）；
3 不同之处
两个均提供替换功能，不同的是Translate提供一对一字符级别的替换，而Replace提供字符串级的替换（当然单一字符也可以看做是字符串）
参考：
--------------------------------------------------------------
TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. 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.
---------------------------------------------------
REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.
Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.
--
