精华内容
下载资源
问答
  • regexp_substr
    2021-05-03 03:55:53

    *

    from tt_ticket

    where 1=1

    and (nvl(

    (select m.mapper_city

    from tt_ticket_city_mapper m

    where m.city = trim((select REGEXP_SUBSTR(t.schedule,‘[^/]+‘,1,1)

    from dual))) ,

    trim((select REGEXP_SUBSTR(t.schedule,‘[^/]+‘,1,1) from dual))

    )

    =

    nvl(

    (select m.mapper_city

    from tt_ticket_city_mapper m

    where m.city = trim(#{departureCity})

    ),

    #{departureCity}

    )

    )

    ]]>

    and(nvl(

    (select m.mapper_city

    from tt_ticket_city_mapper m

    where m.city = trim((select REGEXP_SUBSTR(t.schedule,‘[^/]+‘,1,2) from dual))

    ) ,

    trim((select REGEXP_SUBSTR(t.schedule,‘[^/]+‘,1,2) from dual))

    )

    =

    nvl(

    (select m.mapper_city

    from tt_ticket_city_mapper m

    where m.city = trim(#{arriveCity})

    ),

    #{arriveCity}

    )

    ]]>

    先解释一下:select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual

    比方:

    select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,1) from dual

    返回的是虹桥

    select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,1,2) from dual

    返回的是北京

    第一个參数是要解析的字符串,第二个是正在表达式,第三个表示字符串解析时的起始位置。比方:

    select REGEXP_SUBSTR(‘虹桥/北京’,’[^/]+’,2,1) from dual

    返回的是桥

    第四个參数表示取拆分后数组里的第几个值。

    这种话

    nvl(

    (select m.mapper_city

    from tt_ticket_city_mapper m

    where m.city = trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual))

    ) ,

    trim((select REGEXP_SUBSTR(t.schedule,’[^/]+’,1,1) from dual))

    )

    就能够取出/前面的字符串,比方浦东或虹桥,然后在 tt_ticket_city_mapper中查询得到器映射的城市上海。假设没有的话就是它自己

    等于号后边相同是做一下转换。

    当然,看到这,肯定会有人说为什么不直接存出发城市和到达城市两个字段?那是由于行程可能是 北京/香港/迈阿密/香港/北京 这种数据。

    关于这种数据怎么处理。还没有搞,只是在网上大致看了一下。应该是利用存储过程里边操作比較方便

    大家有什么方法能够分享一下哦

    oracle中REGEXP_SUBSTR方法的使用

    标签:dcl   javascrip   位置   问题:   and   this   length   问题   相同

    本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源:http://www.cnblogs.com/mfmdaoyou/p/7098276.html

    更多相关内容
  • REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(string, pattern, position, occurrence, modifier) string :需要进行正则处理的字符串 pattern :进行匹配的正则表达式 position :起始位置,从第几个...
  • 文章目录1 概述2 匹配规则3 正则函数3.1 regexp_substr()3.2 regexp_instr()3.3 regexp_replace()3.4 regexp_like() 1 概述 1. 作用:处理字符时,很强大 2. 分类:与下列相似,当功能更加强大('支持正则表达式')...

    1 概述

    1. 作用:处理字符时,很强大
    
    2. 分类:与下列相似,但功能更加强大('支持正则表达式'(1) regexp_like   : 同 like 功能相似(模糊 '匹配'(2) regexp_instr  : 同 instr 功能相似(返回字符所在 '下标'(3) regexp_substr : 同 substr 功能相似('截取' 字符串)
       (4) regexp_replace: 同 replace 功能相似( '替换' 字符串)
       -- 使用了 '正则表达式' 替代了老的 '百分比 %' 和 '通配符 _'
       -- 上述 四个 函数,正则用法相似,知晓一个,其它皆可参考
    

    2 匹配规则

    主要的类型如下:

    分组元字符描述
    特殊的\转义字符。“\n”:换行,"\\n":文本 ‘\n’
    .匹配除转义字符外的 任何单字符
    定位元字符^使表达式定位至一行的 开头
    $使表达式定位至一行的 末尾
    量词或重复操作符*匹配 0 次或多次(任意次数)
    ?匹配 0 次或 1 次(最多 1 次)
    +匹配 1 次或多次(至少 1 次)
    {m}正好 匹配 m 次
    {m, }至少 匹配 m 次
    {m, n}匹配 m 到 n
    表达式的替换和分组|替换,通常和分组操作符 () 一起使用
    () 分组,并且 的关系
    [char]字符列表,或者 的关系
    预定义的 posix 字符类[:alpah:]任何字母
    [:lower:]任何小写字母
    [:upper:]任何大写字母
    [:digit:]任何数字,相当于 [0-9]
    [:xdigit:]任何 16 进制的数字,相当于 [0-9a-fA-F]
    [:alnum:]任何字符或数字字符
    [:space:]空白字符,如:回车符,换行符、竖直制表符和换页符
    [:punct:]任何标点符号

    示例:此案例来源于网络,略作修改,挺适合参考的

    1. 查询 value 中以 1 开头 60 结尾的记录并且长度是 7select * from table_name where value like '1____60'; -- 4 个 _
    select * from table_name where regexp_like(value, '1....60');
    
    2. 查询 value 中以 1 开头 60 结尾的记录并且长度是 7 位且全部是数字的记录
       此时,用 like 就不是很好实现了
    select * from table_name where regexp_like(value, '^1[0-9]{4}60$');
    select * from table_name where regexp_like(value, '^1[[:digit:]]{4}60$');
    
    3. 查询 value 中不包含任何数字的记录
    select * from table_name where regexp_like(value, '^[^[:digit:]]+$'); -- [] 内的 ^ 表示 '非'
    
    4. 查询 value 中不是纯数字的记录
    select * from table_name where not regexp_like(value, '^[[:digit:]]+$');
    
    5. 查询以 121b 开头的记录,区分大小写
    select * from table_name where regexp_like(value, '^(12)|^(1b)');
    select * from table_name where regexp_like(value, '^1[2b]');
    
    6. 查询以 121b 开头的记录,不区分大小写
    select * from table_name where regexp_like(value, '^1[2b]', 'i');
    
    7. 查询所有均为 小写字母 或 数字的记录
    select * from table_name where regexp_like(value, '^([a-z]+|[0-9]+)$');
    
    8. 查询包含 空白 的记录
    select * from table_name where regexp_like(value, '[[:space:]]');
    
    9. 查询包含 标点符号 的记录
    select * from table_name where regexp_like(value, '[[:punct:]]');
    

    3 正则函数

    3.1 regexp_substr()

    在这里插入图片描述
    参数解释:

    srcstr    	 : 源字符串 -- source string
    pattern   	 : 正则表达式
    position  	 : 起始位置,默认 1
    occurrence	 : 第几次出现,默认 1(匹配成功的次数,依次递增)
    modifier  	 : 模式('i': 不区分大小写,'c': 区分大小写。 默认 'c')
    subexpression: 含有子表达式 0-9, 默认 0:不含子表达式,1:第一个子表达式,以此类推
    

    例1:

    select regexp_substr('abc,CBA121ABC,cba', ',[^,]+,') rs1, -- ,CBA121ABC,
           regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) rs2 -- 123
      from dual;
    

    例2:拆分邮箱

    with temp_email as (
      select 1 user_no, 'a1@qq.com' email from dual union all
      select 2 user_no, 'bb2@sina.com' email from dual union all
      select 3 user_no, 'ccc3@aliyun.com' email from dual
    )
    select t.user_no 用户编号,
           regexp_substr(t.email,'[[:alnum:]]+') 用户名,
           regexp_substr(t.email, '\@[[:alnum:]]+\.[[:alnum:]]+') 邮箱后缀
           -- 两者等同
           -- regexp_substr(t.email,'[a-zA-Z0-9]+') 用户名2,
           -- regexp_substr(t.email, '\@[a-zA-Z0-9]+\.[a-zA-Z0-9]+') 邮箱后缀2
      from temp_email t;
    

    测试结果:

    用户编号	用户名	邮箱后缀
    1		a1		@qq.com
    2		bb2		@sina.com
    3		ccc3	@aliyun.com
    

    例3:匹配固定电话号码和手机号码

    --*************************************************************
    -- 固定电话格式:0开头 + 2到3位任意数字 + '-' + 6到7位任意数字
    -- 手机号码格式:1开头 + 10位任意数字(11位)
    --*************************************************************
    with temp_phone as (
      select 1 user_no, '0755-11112222' phone_no from dual union all
      select 2 user_no, '055-33334444'  phone_no from dual union all
      select 3 user_no, '0755 55556666' phone_no from dual union all
      select 4 user_no, 'AE86-77778888' phone_no from dual union all
      select 5 user_no, '0755-888999'   phone_no from dual union all
      select 6 user_no, '15866668888'   phone_no from dual union all
      select 7 user_no, '015866668888'  phone_no from dual
    )
    select t.user_no 用户编号,
           t.phone_no 联系方式,
           regexp_substr(t.phone_no,'^0[0-9]{2,3}-[0-9]{6,7}') 固定电话,
           regexp_substr(t.phone_no,'^1[0-9]{10}') 手机号码
      from temp_phone t;
    

    测试结果:
    在这里插入图片描述

    3.2 regexp_instr()

    在这里插入图片描述

    参数解释:

    srcstr    	 : 源字符串 -- source string
    pattern   	 : 正则表达式
    position  	 : 起始位置,默认 1
    occurrence	 : 第几次出现,默认 1(匹配成功的次数,依次递增)
    returnparam  : 返回参数对应的下标(0:第一个位置的下标,非0:最后一个位置的下标)
    modifier  	 : 模式('i': 不区分大小写,'c': 区分大小写。 默认 'c')
    subexpression: 含有子表达式 0-9, 默认 0:不含子表达式,1:第一个子表达式,以此类推
    

    示例:

    select regexp_instr('1,23,456,7890', '[0-9]+', 1, 3) 匹配到的第一个字符下标, -- 6
           regexp_instr('1,23,456,7890', '[0-9]+', 1, 3, 1) 最后一个字符后一位的下标, -- 9
           regexp_instr('1234567890', '(123)(4)(56)(78)', 1, 1, 0, 'i', 4) 匹配到的第四个子表达式 -- 7
      from dual;
    

    查询结果:

    匹配到的第一个字符下标	最后一个字符后一位的下标	匹配到的第四个子表达式
    6						9						7
    

    3.3 regexp_replace()

    在这里插入图片描述

    示例:

    with temp_strings as (   
      select 'abc123' str from dual union all
      select '123abc' str from dual union all
      select 'a1b2c3' str from dual
    )   
    select t.str 源字符串,
           regexp_replace(t.str, '[0-9]', '', 1) 无数字字符串
      from temp_strings t;
    

    查询结果:

    	源字符串	无数字字符串
    1	abc123	abc
    2	123abc	abc
    3	a1b2c3	abc
    

    3.4 regexp_like()

    在这里插入图片描述

    示例:

    with temp_strings as (   
      select 'abc123' str from dual union all
      select '12abcd' str from dual union all
      select 'a1b2c3' str from dual
    )   
    select t.str 连续的三个数字字符
      from temp_strings t
     where regexp_like(t.str, '[0-9]{3}');
    

    查询结果:

    连续的三个数字字符
    abc123
    
    展开全文
  • Oracle使用正则表达式离不开这4个...regexp_like 只能用于条件表达式,和 like 类似,但是使用的正则表达式进行匹配,语法很简单:regexp_substr 函数,和 substr 类似,用于拾取合符正则表达式描述的字符子串,语法...

    Oracle使用正则表达式离不开这4个函数:

    1。regexp_like

    2。regexp_substr

    3。regexp_instr

    4。regexp_replace

    看函数名称大概就能猜到有什么用了。

    regexp_like 只能用于条件表达式,和 like 类似,但是使用的正则表达式进行匹配,语法很简单:

    8442de2ca8223bec2fa7b1d892f95582.gif

    regexp_substr 函数,和 substr 类似,用于拾取合符正则表达式描述的字符子串,语法如下:

    d8706544ec37a88d50bf672ed2dccbb2.gif

    regexp_instr 函数,和 instr 类似,用于标定符合正则表达式的字符子串的开始位置,语法如下:

    11115a0bd1e24bda8cec7f443d5cef33.gif

    regexp_replace 函数,和 replace 类似,用于替换符合正则表达式的字符串,语法如下:

    c308ac165e3bc72a3469de6058d6d6de.gif

    这里解析一下几个参数的含义:

    1。source_char,输入的字符串,可以是列名或者字符串常量、变量。

    2。pattern,正则表达式。

    3。match_parameter,匹配选项。

    取值范围: i:大小写不敏感; c:大小写敏感;n:点号 . 不匹配换行符号;m:多行模式;x:扩展模式,忽略正则表达式中的空白字符。

    4。position,标识从第几个字符开始正则表达式匹配。

    5。occurrence,标识第几个匹配组。

    6。replace_string,替换的字符串。

    说了一堆文绉绉的,现在开始实例演练了,在此之前先建好一个表。

    01 create table tmp as

    02 with data as (

    03   select 'like' as id ,'a9999' as str from dual union all

    04   select 'like'       ,'a9c'          from dual union all

    05   select 'like'       ,'A7007'        from dual union all

    06   select 'like'       ,'123a34cc'     from dual union all

    07   select 'substr'     ,'123,234,345'  from dual union all

    08   select 'substr'     ,'12,34.56:78'  from dual union all

    09   select 'substr'     ,'123456789'    from dual union all

    10   select 'instr'      ,'192.168.0.1'  from dual union all

    11   select 'replace'    ,'(020)12345678' from dual union all

    12   select 'replace'    ,'001517729C28' from dual

    13 )

    14 select * from data ;

    15

    16 select * from tmp ;

    17 ID      STR

    18 ------- -------------

    19 like    a9999

    20 like    a9c

    21 like    A7007

    22 like    123a34cc

    23 substr  123,234,345

    24 substr  12,34.56:78

    25 substr  123456789

    26 instr   192.168.0.1

    27 replace (020)12345678

    28 replace 001517729C28

    regexp_like 例子:

    01 select str from tmp where id='like' and regexp_like(str,'A\d+','i'); -- 'i' 忽略大小写

    02 STR

    03 -------------

    04 a9999

    05 a9c

    06 A7007

    07 123a3

    4cc

    08

    09 select str from tmp where id='like' and regexp_like(str, 'a\d+');

    10 STR

    11 -------------

    12 a9999

    13 a9c

    14 123a34cc

    15

    16 select str from tmp where id='like' and regexp_like(str,'^a\d+');

    17 STR

    18 -------------

    19 a9999

    20 a9c

    21

    22 select str from tmp where id='like' and regexp_like(str,'^a\d+$');

    23 STR

    24 -------------

    25 a9999

    regexp_substr 例子:

    01 col str format a15;

    02 select

    03   str,

    04   regexp_substr(str,'[^,]+')     str,

    05   regexp_substr(str,'[^,]+',1,1) str,

    06   regexp_substr(str,'[^,]+',1,2) str,  -- occurrence 第几个匹配组

    07   regexp_substr(str,'[^,]+',2,1) str   -- position 从第几个字符开始匹配

    08 from tmp

    09 where id='substr';

    10 STR             STR             STR             STR             STR

    11 --------------- --------------- --------------- --------------- ---------------

    12 123,234,345     123             123             234             23

    13 12,34.56:78     12              12              34.56:78        2

    14 123456789       123456789       123456789                       23456789

    15

    16 select

    17   str,

    18   regexp_substr(str,'\d')        str,

    19   regexp_substr(str,'\d+'  ,1,1) str,

    20   regexp_substr(str,'\d{2}',1,2) str,

    21   regexp_substr(str,'\d{3}',2,1) str

    22 from tmp

    23 where id='substr';

    24 STR             STR             STR             STR             STR

    25 --------------- --------------- --------------- --------------- ---------------

    26 123,234,345     1               123             23              234

    27 12,34.56:78     1               12              34

    28 123456789       1               123456789       34              234

    29

    30

    31 select regexp_substr('123456789','\d',1,level) str  --取出每位数字,有时这也是行转列的方式

    32 from dual

    33 connect by level<=9

    34 STR

    35 ---------------

    36 1

    37 2

    38 3

    39 4

    40 5

    41 6

    42 7

    43 8

    44 9

    regex_instr 例子:

    01 col ind format 9999;

    02 select

    03   str,

    04   regexp_instr(str,'\.'    ) ind ,

    05   regexp_instr(str,'\.',1,2) ind ,

    06   regexp_instr(str,'\.',5,2) ind

    07 from tmp where id='instr';

    08 STR               IND   IND   IND

    09 --------------- ----- ----- -----

    10 192.168.0.1         4     8    10

    11

    12 select

    13   regexp_instr('192.168.0.1','\.',1,level) ind ,  -- 点号. 所在的位置

    14   regexp_instr('192.168.0.1','\d',1,level) ind    -- 每个数字的位置

    15 from dual

    16 connect by level <=  9

    17   IND   IND

    18 ----- -----

    19     4     1

    20     8     2

    21    10     3

    22     0     5

    23     0     6

    24     0     7

    25     0     9

    26     0    11

    27     0     0

    regex_replace 例子:

    01 select

    02   str,

    03   regexp_replace(str,'020','GZ') str,

    04   regexp_replace(str,'(\d{3})(\d{3})','') str -- 将第一、第二捕获组交换位置,用尖括号标识出来

    05 from tmp

    06 where id='replace';

    07 STR             STR             STR

    08 --------------- --------------- ---------------

    09 (020)12345678   (GZ)12345678    (020)<456123>78

    10 001517729C28    001517729C28    <517001>729C28

    综合应用的例子:

    01 col row_line format a30;

    02 with sudoku as (

    03   select '020000080568179234090000010030040050040205090070080040050000060289634175010000020' as line

    04   from dual

    05 ),

    06 tmp as (

    07   select regexp_substr(line,'\d{9}',1,level) row_line,

    08   level col

    09   from sudoku

    10   connect by level<=9

    11 )

    12 select regexp_replace( row_line ,'(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)(\d)','\1 \2 \3 \4 \5 \6 \7 \8 \9') row_line

    13 from tmp

    14

    15 ROW_LINE

    16 ------------------------------

    17 0 2 0 0 0 0 0 8 0

    18 5 6 8 1 7 9 2 3 4

    19 0 9 0 0 0 0 0 1 0

    20 0 3 0 0 4 0 0 5 0

    21 0 4 0 2 0 5 0 9 0

    22 0 7 0 0 8 0 0 4 0

    23 0 5 0 0 0 0 0 6 0

    24 2 8 9 6 3 4 1 7 5

    25 0 1 0 0 0 0 0 2 0

    展开全文
  • /*ORACLE中的支持正则表达式的函数主要有下面四个:1,REGEXP_LIKE :与LIKE的功能相似2,REGEXP_INSTR :与INSTR的功能相似3,REGEXP_SUBSTR :与SUBSTR的功能相似4,REGEXP_REPLACE :与REPLACE的功能相似它们在...

    /*

    ORACLE中的支持正则表达式的函数主要有下面四个:

    1,REGEXP_LIKE :与LIKE的功能相似

    2,REGEXP_INSTR :与INSTR的功能相似

    3,REGEXP_SUBSTR :与SUBSTR的功能相似

    4,REGEXP_REPLACE :与REPLACE的功能相似

    它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同,

    但是它们使用POSIX 正则表达式代替了老的百分号(%)和通配符(_)字符。

    POSIX 正则表达式由标准的元字符(metacharacters)所构成:

    '^' 匹配输入字符串的开始位置,在方括号表达式中使用,此时它表示不接受该字符集合。

    '$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性,则 $ 也匹

    配 ' ' 或 ' '。

    '.' 匹配除换行符之外的任何单字符。

    '?' 匹配前面的子表达式零次或一次。

    '+' 匹配前面的子表达式一次或多次。

    '*' 匹配前面的子表达式零次或多次。

    '|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的

    字符串。

    '( )' 标记一个子表达式的开始和结束位置。

    '[]' 标记一个中括号表达式。

    '{m,n}' 一个精确地出现次数范围,m=

    出现m次。

    um 匹配 num,其中 num 是一个正整数。对所获取的匹配的引用。

    字符簇:

    [[:alpha:]] 任何字母。

    [[:digit:]] 任何数字。

    [[:alnum:]] 任何字母和数字。

    [[:space:]] 任何白字符。

    [[:upper:]] 任何大写字母。

    [[:lower:]] 任何小写字母。

    [[:punct:]] 任何标点符号。

    [[:xdigit:]] 任何16进制的数字,相当于[0-9a-fA-F]。

    各种操作符的运算优先级

    \转义符

    (), (?:), (?=), [] 圆括号和方括号

    *, +, ?, {n}, {n,}, {n,m} 限定符

    ^, $, anymetacharacter 位置和顺序

    |

    */

    --创建表

    create table fzq

    (

    id varchar(4),

    value varchar(10)

    );

    --数据插入

    insert into fzq values

    ('1','1234560');

    insert into fzq values

    ('2','1234560');

    insert into fzq values

    ('3','1b3b560');

    insert into fzq values

    ('4','abc');

    insert into fzq values

    ('5','abcde');

    insert into fzq values

    ('6','ADREasx');

    insert into fzq values

    ('7','123 45');

    insert into fzq values

    ('8','adc de');

    insert into fzq values

    ('9','adc,.de');

    insert into fzq values

    ('10','1B');

    insert into fzq values

    ('10','abcbvbnb');

    insert into fzq values

    ('11','11114560');

    insert into fzq values

    ('11','11124560');

    --regexp_like

    --查询value中以1开头60结束的记录并且长度是7位

    select * from fzq where value like '1____60';

    select * from fzq where regexp_like(value,'1....60');

    --查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

    --使用like就不是很好实现了。

    select * from fzq where regexp_like(value,'1[0-9]{4}60');

    -- 也可以这样实现,使用字符集。

    select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');

    -- 查询value中不是纯数字的记录

    select * from fzq where not regexp_like(value,'^[[:digit:]]+$');

    -- 查询value中不包含任何数字的记录。

    select * from fzq where regexp_like(value,'^[^[:digit:]]+$');

    --查询以12或者1b开头的记录.不区分大小写。

    select * from fzq where regexp_like(value,'^1[2b]','i');

    --查询以12或者1b开头的记录.区分大小写。

    select * from fzq where regexp_like(value,'^1[2B]');

    -- 查询数据中包含空白的记录。

    select * from fzq where regexp_like(value,'[[:space:]]');

    --查询所有包含小写字母或者数字的记录。

    select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');

    --查询任何包含标点符号的记录。

    select * from fzq where regexp_like(value,'[[:punct:]]');

    /*

    理解它的语法就可以了。其它的函数用法类似。

    1* select regexp_substr('1234-23-45','[[:alnum:]]+') from dual

    SQL> /

    REGE

    ----

    1234

    1* select regexp_substr('1234abc-23-45','[[:alnum:]]+') from dual

    SQL> /

    REGEXP_

    -------

    1234abc

    1* select regexp_substr('1234abc-23-45','[[:alnum:]]+') from dual

    SQL> /

    REGEXP_

    -------

    1234abc

    select REGEXP_SUBSTR('123-456-7890', '-[^-]+-' )

    "REGEXP_SUBSTR"

    from DUAL;

    REGEX

    -----

    -456-

    Oracle中如何使用REGEXP_SUBSTR函数

    REGEXP_SUBSTR函数格式如下:

    function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

    __srcstr :需要进行正则处理的字符串

    __pattern :进行匹配的正则表达式

    __position :起始位置,从第几个字符开始正则表达式匹配(默认为1)

    __occurrence :标识第几个匹配组,默认为1

    __modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)

    实际应用如下:在oracle中,使用一条语句实现将'34,56,-23'拆分成'34','56','-23'的集合。

    --1、查询使用正则分割后的第一个值,也就是34

    SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL;

    --结果是:34

    --2、查询使用正则分割后的最后一个值,也就是-23

    SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL;

    --结果是:-23

    --3、获取一个多个数值的列,从而能够让结果以多行的形式展示出来

    SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=7;

    --结果是:

    --1

    --2

    --3

    --4

    --5

    --6

    --7

    --4、将上面REGEXP_SUBSTR的occurrence关联

    SELECT NVL(REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i'), 'NULLL') AS STR FROM DUAL CONNECT BY LEVEL <= 7;

    --结果是:

    --34

    --56

    ---23

    --null

    --null

    --mull

    --null

    --5、优化上面的SQL语句,让生成的行的数量符合实际情况

    SELECT REGEXP_SUBSTR('34,56,-23', '[^,]+', 1, LEVEL, 'i') AS STR FROM DUAL CONNECT BY LEVEL <= LENGTH('34,56,-23') - LENGTH(REGEXP_REPLACE('34,56,-23', ',', '')) + 1;

    --结果是:

    --34

    --56

    ---23

    一字段含有逗号分隔的串,如何把这条记录按分隔符分成多条记录

    select T_PERSON_ID,

    T_PERSON_NAME,

    regexp_substr(T_TYPE_ID, '[^,]+', 1, level) T_TYPE_ID

    from t_info

    connect by level <= regexp_count(T_TYPE_ID, ',') + 1

    and T_PERSON_ID = prior T_PERSON_ID

    and prior dbms_random.value is not null;

    oracle简单按分隔符单行转多行sql

    今天看到篇博客, http://www.cnblogs.com/hankuikui/p/3523013.html ,说的是按分隔符把一列转成多行,原文给出的方法是 :

    with tmp_t as

    (select '1001' as userId, '10,12,15' as workgroups

    from dual

    union

    select '1002' as userId, '2,4,5' as workgroups from dual)

    select userid,

    substr(tempgroups,

    instr(tempgroups, ',', 1, lv) + 1,

    instr(tempgroups, ',', 1, lv + 1) -

    (instr(tempgroups, ',', 1, lv) + 1))

    from (select userid,

    ',' || workgroups || ',' AS tempgroups,

    length(workgroups || ',') -

    nvl(length(replace(workgroups, ',')) , 0) AS groupcount

    FROM tmp_t) a,

    (select LEVEL lv from dual CONNECT BY LEVEL <= 5) b

    where b.lv <= a.groupcount

    order by userid, lv

    结果为 :

    0818b9ca8b590ca3270a3433284dd417.png

    使用了CONNECT BY 构造1-5的lv值和instr截取,详细分析见原文 。

    个人看到connect by level <=5总想这替换掉5,只要得到表中最大的,个数就可以了,如下 :

    with tmp_t as

    (select '1001' as userid, '10,12,15' as workgroups

    from dual

    union

    select '1002' as userid, '2,4,5' as workgroups from dual)

    select userid,

    substr(tempgroups,

    instr(tempgroups, ',', 1, lv) + 1,

    instr(tempgroups, ',', 1, lv + 1) -

    (instr(tempgroups, ',', 1, lv) + 1))

    from (select userid,

    ',' || workgroups || ',' as tempgroups,

    length(workgroups || ',') -

    nvl(length(replace(workgroups, ',')) , 0) as groupcount

    from tmp_t) a,

    (select level lv

    from dual

    connect by level <= (select max(length(workgroups) -

    length(replace(workgroups, ',', ''))) + 1

    from tmp_t)) b

    where b.lv <= a.groupcount

    order by userid, lv

    上面的写法简单易懂,恰巧昨天看到篇文章,里面有更好的写法,原文链接 :

    http://www.dewen.org/q/16184/%E4%BB%A5%E5%A4%8D%E6%9D%82%E6%95%B0%E6%8D%AE%E5%AD%97%E6%AE%B5%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2

    更好的写法如下 :

    with tmp_t as

    (select '1001' as userid, '10,12,15' as workgroups

    from dual

    union

    select '1002' as userid, '2,4,5' as workgroups from dual)

    select userid,

    --regexp_substr(str,reg,起始位置 第几次)

    regexp_substr(workgroups, '[^,]+', 1, level) clr

    from tmp_t

    connect by level <= regexp_count(workgroups, ',') + 1

    --regexp_count(workgroups, ',') 统计字符串中,的数量

    --用法参考http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF51665

    and userid = prior userid

    and prior dbms_random.value is not null 相当简洁,很不错的写法,结果和上面的一样 。 全文完 。

    展开全文
  • REGEXP_SUBSTR REGEXP_SUBSTR (source_string, pattern [,position [,occurrence [,match_parameter]]] ) Position:指定在字符串中准确位置,即开始匹配的字符的位置,默认为1 Occurrence:指定在源字符串匹配过程...
  • function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) string:需要进行正则处理的字符串 pattern:进行匹配的正则表达式 position:起始位置,从字符串的第几个字符开始正则表达式匹配(默认...
  • Oracle中REGEXP_SUBSTR函数Oracle 中REGEXP_SUBSTR函数的使用说明:题目如下:在 oracle 中,使用一条语句实现将’17,20,23’拆分成’17’,’20’,’23’的集合。REGEXP_SUBSTR函数格式如下:function REGEXP_...
  • 场景①: 可以用instr查找字符位置,然后用substr进去...也可以用正则表达式regexp_substr,实现mysql中的substring_index效果: 1、取分割后的第一个值: SELECT REGEXP_SUBSTR('123,,,ABC,!@#,,,', '[^,]+') FR
  • oracle REGEXP_SUBSTR函数

    千次阅读 2021-03-04 00:06:19
    REGEXP_SUBSTR函数格式如下:function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)__srcstr :需要进行正则处理的字符串__pattern :进行匹配的正则表达式__position :起始位置,从第几个字符...
  • REGEXP_SUBSTR(text, pattern [,position [,occurence [,mode]]]) REGEXP_INSTR?(text, pattern [,position [,occurence [,return_end [,mode]]]]) REGEXP_REPLACE?(text, pattern, replace [,position [,occurence ...
  • In Oracle, I can use REGEXP_SUBSTR for this problem. How would I solve this in MySQL? Edit 1 I am using LOCATE and SUBSTR to solve the problem. The code is ugly. Ten minutes after writing the code, I...
  • REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) 参数说明: String, 原字符串 pattern, 进行匹配的正则表达式 position, 起始位置, 从第几个字符开始匹配查找, 默认为 1 occurrence, 取第...
  • regexp_substr

    2021-03-06 10:55:54
    regexp_substr与层次查询结合使用,生成特定行 Select REGEXP_SUBSTR('COL,CLO1,COL2', '[^,]+', 1, level) a FROM DUAL CONNECT BY level <= LENGTH('COL,CLO1,COL2') - LENGTH(REGEXP_...
  • 5个参数第一个是输入的字符串第二个是正则表达式第三个是标识从第几个字符开始正则表达式匹配。(默认为1)第四个是标识第几个匹配组。(默认为1)第五个是是取值... select * from test_reg_substr;A---------------...
  • Oracle中REGEXP_SUBSTR函数

    2021-05-08 19:10:02
    Oracle中REGEXP_SUBSTR函数Oracle中REGEXP_SUBSTR函数的使用说明:题目如下:在oracle中,使用一条语句实现将‘17,20,23‘拆分成‘17‘,‘20‘,‘23‘的集合。REGEXP_SUBSTR函数格式如下:function REGEXP_SUBSTR...
  • regexp_substr()用法

    2020-12-21 13:22:50
    select count() as samplingrate from ( with aa as( select regexp_substr(bb.baselinesubid,’[^,]+’,1,ROWNUM) as idnum,bb. from (select * from FPS_SAMPLINGSETTING_ACTIVE a where 1=1 and a.PRODUCTNAME =...
  • select regexp_substr(cbbmmc, '[^,]+', 1, rownum) cbbmmc, regexp_substr(cbbmid, '[^,]+', 1, rownum) cbbmid, month from t connect by rownum (regexp_replace(cbbmmc, '[^,]+')) +1 ``` 其中 t的...
  • 行转列函数:REGEXP_SUBSTR function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) srcstr :需要进行正则处理的字符串 pattern :进行匹配的正则表达式 position :起始位置,从第几个字符...
  • 在update语句中使用regexp_substr函数实现 regexp_substr(source_char,pattern,position,occurrence,match_parameter) regexp_substr函数有五个可选参数。他们的作用分别如下: source_char是目标字符串。必选参数...
  • Oracle REGEXP_SUBSTR()函数

    千次阅读 2021-11-11 18:34:30
    REGEXP_SUBSTR(String, pattern, position,occurrence, modifier) 一、参数解释 String:需要进行正则处理的字符串。 pattern:正则表达式。 position:起始位置(从字符串的第几个开始正则,默认为1,注:数据库中的...
  • hive实现regexp_substr()

    千次阅读 2020-11-09 19:35:02
    首先需要清楚regexp_substr()的作用,先看官方解释REGEXP_SUBSTR通过允许您在字符串中搜索正则表达式模式来扩展功能的功能。它也与相似REGEXP_INSTR,但是它不返回子字符串的位置,而是返回子字符串本身。如果您需要...
  • select regexp_substr(:v_sn0,’[^;]+’,1,level) as str from dual connect by regexp_substr(:v_sn0,’[^;]+’,1,level) is not null union select regexp_substr(:v_sn1,’[^;]+’,1,level) as str from dual ...
  • 切割SQL如下: select decode(regexp_instr(c1,'\.'),0,regexp_substr(c1,'[[:digit:]]+'), regexp_substr(c1,'[[:digit:]]+')||'.'||regexp_substr(c1,'[[:digit:]]+',regexp_instr(c1,'\.')) ) c1_val, regexp_...
  • 1、regexp_substr函数,通过正则来拆分字符串,函数用法为:(必须是oracle 10g+的版本才支持) REGEXP_SUBSTR函数格式如下: function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) __srcstr :...
  • 函数:REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) string:需要处理的字符串 pattern:正则表达式 position:起始位置,从字符串的第几个字符开始正则表达式匹配(默认为1) occurrence:获取...
  • 使用gbase 8s中REGEXP_REPLACE/REGEXP_SUBSTR/REGEXT_INSTR函数的用法
  • 5个参数第一个是输入的字符串第二个是正则表达式第三个是标识从第几个字符开始正则表达式匹配。(默认为1)第四个是标识第几个匹配组。(默认为1)第五个是是取值... select * from test_reg_substr;A---------------...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 41,519
精华内容 16,607
关键字:

regexp_substr

友情链接: 372691.rar