ORACLE常用函数学习记录

SUBSTR

用于截取字符串：

SUBSTR(‘目标字符串’, , )
如： SUBSTR(‘192.168.0.1’,3,3) ———> 返回2.1

SUBSTR(‘192.168.0.1’,0,1)和SUBSTR(‘192.168.0.1’,1,1)返回结果都是：1
如果开始位是0，ORACLE会把0当做1对待

使用示例：

遍历字符串(使用substr依次裁剪)

    SELECT SUBSTR(a.str,b.seq,1)  as c
    FROM   (select str from tmp_hywel_test where str is not null) a ,
           (select rownum as seq from tmp_hywel_test) b
    where  b.seq<=length(a.str);
    --（遍历字符串，Oracle还可以使用level+connect by实现）

REGEXP_LIKE

功能和like类似

regexp_like(str,pattern[,match_option])

其基本功能是在str中查找pattern，如果能找到返回true否则返回false,这里的可选参数match_option可以有如下几种形式：

‘c’ 表明进行匹配时区分大小写(这也是默认选项)。

‘i’ 表明在匹配时不区分大小写。

‘n’ 表明允许使用匹配任何字符串的元数据，即’.'。

‘m’将x作为一个包含多行的字符串。

**使用示例：** : 寻找名字是以’M’或者’m’打头的那些员工的名字

select * from emp where regexp_like(ename,’^j’,'i’)

REGEXP_INSTR

字符串中搜索正则表达式模式并返回指示匹配子字符串的开始位置的整数。如果未找到匹配项，此函数将返回 0

REGEXP_INSTR ( source_string, pattern [, position ] )

source_string

要搜索的字符串表达式（如列名称）。

pattern

表示 SQL 标准正则表达式模式的字符串文本。

position

指示在 source_string 中开始搜索的位置的正整数。此位置基于字符数而不是字节数，这是为了将多字节字符作为单字符计数。默认值为 1。如果 position 小于 1，则搜索从 source_string 的第一个字符开始。如果 position 大于 source_string 中字符的数量，则结果为 0。

使用示例：

select id, regexp_instr(str1,'是',1) as result from tmp_hywel_test
    表结构：
        id  | str1
        1   | a
        2   | 我是好人！
        3   | 我,是好,人！

    返回结果： 
        1       0
        2       2
        3       3

REGEXP_SUBSTR

通过搜索正则表达式模式返回从字符串中提取的字符 * 语法*

REGEXP_SUBSTR ( source_string, pattern [, position ] )

参数 source_string

要搜索的字符串表达式（如列名称）。

pattern

表示 SQL 标准正则表达式模式的字符串文本。

position

指示在 source_string 中开始搜索的位置的正整数。此位置基于字符数而不是字节数，这是为了将多字节字符作为单字符计数。默认值为 1。如果 position 小于 1，则搜索从 source_string 的第一个字符开始。如果 position 大于 source_string 中的字符数量，则结果为空字符串 (“”)。

使用示例

select email, regexp_substr(email,'@[^.]*') from users limit 5;
-- 返回email中的邮箱格式
   email                                    | regexp_substr
--------------------------------------------+----------------
Suspendisse.tristique@nonnisiAenean.edu     | @nonnisiAenean
sed@lacusUtnec.ca                           | @lacusUtnec
elementum@semperpretiumneque.ca             | @semperpretiumneque
Integer.mollis.Integer@tristiquealiquet.org | @tristiquealiquet
Donec.fringilla@sodalesat.org               | @sodalesat

REGEXP_REPLACE

在字符串中搜索正则表达式模式并将该模式的每个匹配项替换为指定字符串 语法

REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] )

参数 source_string

要搜索的字符串表达式（如列名称）。

pattern

表示 SQL 标准正则表达式模式的字符串文本。

replace_string

将替换模式的每个匹配项的字符串表达式（如列名称）。默认值是空字符串 (“”)。

position

指示在 source_string 中开始搜索的位置的正整数。此位置基于字符数而不是字节数，这是为了将多字节字符作为单字符计数。默认值为 1。如果 position 小于 1，则搜索从 source_string 的第一个字符开始。如果 position 大于 source_string 中的字符数量，则结果为 source_string。

使用示例

select email, regexp_replace( email, '@.*\\.(org|gov|com)$','@youdu.com');

--将所有邮箱后缀替换为@youdu.com
              email                | regexp_replace 
-----------------------------------+----------------  
  DonecFri@semperpretiumneque.com  | DonecFri@youdu.com
  mk1wait@UniOfTech.org            | mk1wait@youdu.com
  sed@redshiftemails.com           | sed@youdu.com
  bunyung@integermath.gov          | bunyung@youdu.com
  tomsupporter@galaticmess.org     | tomsupporter@youdu.com

REGEXP_COUNT

在字符串中搜索正则表达式模式并返回指示该模式在字符串中出现的次数的整数 语法

REGEXP_COUNT ( source_string, pattern [, position ] )

参数 source_string

要搜索的字符串表达式（如列名称）。

pattern

表示 SQL 标准正则表达式模式的字符串文本。

position

指示在 source_string 中开始搜索的位置的正整数。此位置基于字符数而不是字节数，这是为了将多字节字符作为单字符计数。默认值为 1。如果 position 小于 1，则搜索从 source_string 的第一个字符开始。如果 position 大于 source_string 中字符的数量，则结果为 0。

使用示例

计算三个连续字母序列出现的次数（出现过后则不再计算）:

select regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');  

regexp_count  
--------------             
8