比较有用的两个oracle函数INSTR和translate的妙用

Oracle中INSTR的用法：

----------------------------------------------------------------- 
INSTR方法的格式为
INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)
返回找到的位置，如果找不到则返回0.
例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 在字符串中查找'OR'，从第三个字符位置开始查找"OR"，取第三个字后第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "aaa" FROM DUAL的显示结果是

Instring
——————
14

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/E_wsq/archive/2008/09/26/2983102.aspx

----------------------------------------------------------------- 

 

oracle的substr函数的用法：
 取得字符串中指定起始位置和长度的字符串  

substr( string, start_position, [ length ] )
 如:
     substr('This is a test', 6, 2)     would return 'is'
     substr('This is a test', 6)     would return 'is a test'
     substr('TechOnTheNet', -3, 3)     would return 'Net'
     substr('TechOnTheNet', -6, 3)     would return 'The'
 
select substr('Thisisatest', -4, 2) value from dual

-----------------------------------------------------------

Oracle提供了一个字符替换函数translate，不同于replace函数的是，translate函数是字符级别的替换，而不是字符串的替换。

其语法如下：

TRANSLATE ( expr , from_string , to_string )

简单的说就是对expr内容，用to_string中的字符逐一替换from_string 中的字符，举例说明如下：

SQL> select translate('123456789','456','abc') from dual;

TRANSLATE
---------
123abc789

SQL> select translate('123456789','456','ab') from dual;

TRANSLAT
--------
123ab789

SQL> select translate('123456789','4564','a') from dual;

TRANSLAT
-------
123a789

SQL> select translate('123456789','4564','abcd') from dual;

TRANSLATE
---------
123abc789

可以看到translate函数是以字符为单位逐一替换的，从第二、三个例子可以看出，如果第三个参数to_string的长度小于第二个参数from_string，那么from_string的后边超出的部分都被替换为空值。从第三、四个例子可见，如果某一个字符多次出现，则以第一次替换的内容为准。

以此可以实现的一个有用的功能是统计一个字符串中某个字符出现的次数：

select length(translate('expl','x'||'expl','x')) from dual