代码之家  ›  专栏  ›  技术社区  ›  zundarz

在字段中带有空格的制表符分隔记录上使用regexp\u substr

  •  0
  • zundarz  · 技术社区  · 8 年前

    使用Oracle 12c时,如何使用

    我用这个作为参考: Oracle Regex

    这是我的问题

    with tab_delimited_record as
     (select 'Field1    Field2  This is field3 and contains spaces  Field4' as record_with_fields from dual) 
     select record_with_fields,
            regexp_substr('\S+',1,3) as field3a, -- Expect ==>This is field3...
            regexp_substr('\t+',1,3) as field3b, -- Expect==>This is field3...
            regexp_substr('[[::space::]]+',1,3) as field_3c -- Another version
      from  tab_delimited_record
    

    期望的结果

    Field1 Field2这是field3,包含空格Field4

    2 回复  |  直到 8 年前
        1
  •  1
  •   Gary_W    8 年前

    我相信你在找这样的东西。请注意,本例返回所有字段,但当然,如果您只需要选择field3就可以了。CTE用制表符分隔的字段构建字符串。然后查询使用regex\u substr获取第n个(第4个参数)字符串,后跟制表符或行尾。

    with tab_delimited_record(record_with_fields) as (
      select 'Field1'||chr(09)||'Field2'||chr(09)||'This is field3 and contains spaces'||chr(09)||'Field4' from dual
    ) 
    select record_with_fields,
           regexp_substr(record_with_fields, '(.*?)('||chr(09)||'|$)', 1, 1, null, 1) as field_1, 
           regexp_substr(record_with_fields, '(.*?)('||chr(09)||'|$)', 1, 2, null, 1) as field_2, 
           regexp_substr(record_with_fields, '(.*?)('||chr(09)||'|$)', 1, 3, null, 1) as field_3,
           regexp_substr(record_with_fields, '(.*?)('||chr(09)||'|$)', 1, 4, null, 1) as field_4
    from  tab_delimited_record;
    
        2
  •  1
  •   stack0114106    8 年前

    在使用Oracle SQL时,不能按字面意思使用'\t'。您需要中断字符串,使用chr(09)(ascii制表符),然后构造字符串。试试这个

    with tab_delimited_record as
     (select 'Field1'||chr(09)||'Field2'||chr(09)||'This is field3 and contains spaces'||chr(09)||'Field4' as record_with_fields from dual) 
        select record_with_fields,
            regexp_substr(record_with_fields,'(\S+)\s+(\S+)\s+(.+)\s+',1,1,'',3) as field3a, -- Expect ==>This is field3...
            regexp_substr(record_with_fields,'(\S+)'||chr(09)||'(\S+)'||chr(09)||'(.+)\s+',1,1,'',3) as field3b, -- Expect==>This is field3...
            regexp_substr(record_with_fields,'(\S+)[[:space:]]+(\S+)[[:space:]]+(.+)[[:space:]]+',1,1,'',3) as field_3c -- Another version
      from  tab_delimited_record
    
        3
  •  1
  •   Marcin Szczepaniak    6 年前

    正则表达式的另一个版本:

    with tab_delimited_record(record_with_fields) as (
      select 'Field1'||chr(09)||'Field2'||chr(09)||'This is field3 and contains spaces'||chr(09)||'Field4' from dual
    ) 
    select record_with_fields,
           regexp_substr(record_with_fields, '[^'||chr(09)||']+', 1, 3) as field_3
    from  tab_delimited_record;