This discussion is archived
7 Replies Latest reply: Aug 26, 2008 3:57 AM by Aketi Jyuuzou RSS

previously word

656678 Newbie
Currently Being Moderated
Hi,
I have a table like this;
ID (int), Field1 (varchar2)
---------
1, abc_de_XXX_fg
2, abcd_e_XXX
3, a_b_XXX_c_d
4, a_b_cde
5, XXX_a_b_c
6, abcdef_XXX_ghij_klm_n
...
I want find previously word ("_" is word seperator) before XXX.
Results by this example:
ID (int), Field1 (varchar2), RESULT
---------
1, abc_de_XXX_fg, de
2, abcd_e_XXX, e
3, a_b_XXX_c_d, b
4, a_b_cde, null
5, XXX_a_b_c, null
6, abcdef_XXX_ghij_klm_n, abcdef
Thanks.
  • 1. Re: previously word
    Solomon Yakobson Guru
    Currently Being Moderated
    Since you didnot post version, I'll assume it is 10g:
    select  id,field1,regexp_replace (field1,'(^.*)((_)(.*)(_XXX_.*))','\4') result from tbl;
    SY.
  • 2. Re: previously word
    isotope Pro
    Currently Being Moderated
    Results by this example:
    ID (int), Field1 (varchar2), RESULT
    ---------
    1, abc_de_XXX_fg, de
    2, abcd_e_XXX, e
    3, a_b_XXX_c_d, b
    4, a_b_cde, null
    5, XXX_a_b_c, null
    6, abcdef_XXX_ghij_klm_n, abcdef
    test@XE>
    test@XE> @test
    test@XE> --
    test@XE> with t as (
      2    select 1 as id, 'abc_de_XXX_fg' as field   from dual union all
      3    select 2, 'abcd_e_XXX'                     from dual union all
      4    select 3, 'a_b_XXX_c_d'                    from dual union all
      5    select 4, 'a_b_cde'                        from dual union all
      6    select 5, 'XXX_a_b_c'                      from dual union all
      7    select 6, 'abcdef_XXX_ghij_klm_n'          from dual union all
      8    select 7, 'lm_no_pq_rs_tu_vw_XXX_yz_ab_cd' from dual)
      9  --
     10  select id,
     11         field,
     12         regexp_replace(field,'((((.*)_)*(.*)_)*XXX)*.*','\5') as result
     13  from t;
    
            ID FIELD                          RESULT
    ---------- ------------------------------ ------------------------------
             1 abc_de_XXX_fg                  de
             2 abcd_e_XXX                     e
             3 a_b_XXX_c_d                    b
             4 a_b_cde
             5 XXX_a_b_c
             6 abcdef_XXX_ghij_klm_n          abcdef
             7 lm_no_pq_rs_tu_vw_XXX_yz_ab_cd vw
    
    7 rows selected.
    
    test@XE>
    test@XE>
    isotope

    Sorry, forgot to mention - regular expressions work only in versions 10g and higher.

    Edited by: user9504903 on Aug 25, 2008 2:29 PM
  • 3. Re: previously word
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    This regular expression '(^.*)((_)(.*)(_XXX_.*))' probably doesn't work for this case 'abcdef_XXX_ghij_klm_n' or this 'abcd_e_XXX' and doesn't return null in case of no match.

    How about this, without regular expression:
    SQL>
    SQL> with tbl as (
      2  select 1 as id, 'abc_de_XXX_fg' as field1 from dual union all
      3  select 2 as id, 'abcd_e_XXX' as field1 from dual union all
      4  select 3 as id, 'a_b_XXX_c_d' as field1 from dual union all
      5  select 4 as id, 'a_b_cde' as field1 from dual union all
      6  select 5 as id, 'XXX_a_b_c' as field1 from dual union all
      7  select 6 as id, 'abcdef_XXX_ghij_klm_n' as field1 from dual
      8  )
      9  select
     10  id,
     11  field1,
     12  substr(
     13    substr(
     14      field1,
     15      1,
     16      instr(
     17        field1,
     18        '_XXX'
     19      ) - 1
     20    ),
     21    nvl(
     22      instr(
     23        substr(
     24          field1,
     25          1,
     26          instr(
     27            field1,
     28            '_XXX'
     29          ) - 1
     30        ),
     31        '_',
     32        -1
     33      ) + 1,
     34      1
     35    )
     36  ) as result from tbl;
    
            ID FIELD1                RESULT
    ---------- --------------------- ---------------------
             1 abc_de_XXX_fg         de
             2 abcd_e_XXX            e
             3 a_b_XXX_c_d           b
             4 a_b_cde
             5 XXX_a_b_c
             6 abcdef_XXX_ghij_klm_n abcdef
    
    6 rows selected.
    
    SQL>
    Regards,
    Randolf

    Oracle related stuff:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle:
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 4. Re: previously word
    MaximDemenko Pro
    Currently Being Moderated
    regexp_replace(field,'((((.*)_)*(.*)_)*XXX)*.*','\5')
    Very nice!

    Best regards

    Maxim
  • 5. Re: previously word
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    create table t(ID,str) as
    select 1 ID,'abc_de_XXX_fg' str from dual union
    select 2   ,'abcd_e_XXX' from dual union
    select 3   ,'a_b_XXX_c_d' from dual union
    select 4   ,'a_b_cde' from dual union
    select 5   ,'XXX_a_b_c' from dual union
    select 6   ,'abcdef_XXX_ghij_klm_n' from dual;
    
    col newstr for a30
    
    select ID,str,
    RegExp_Replace(str,'^(([^_]+)_)+XXX.*$|^.*$','\2') as newstr
      from t;
    
    ID  STR                    NEWSTR
    --  ---------------------  --------
     1  abc_de_XXX_fg          de
     2  abcd_e_XXX             e
     3  a_b_XXX_c_d            b
     4  a_b_cde                null
     5  XXX_a_b_c              null
     6  abcdef_XXX_ghij_klm_n  abcdef
    I used recapture B-)
    I recommend above solution which uses case expression ;-)
    select ID,str,
    case when instr(str,'_XXX') != 0
         then RegExp_Replace(str,'^(([^_]+)_)+XXX.*$','\2')
    end as newstr
      from t;
  • 6. Re: previously word
    60660 Journeyer
    Currently Being Moderated
    I would simplify that regular expression to this:
    RegExp_Replace(str, '([^_]+)_XXX|.', '\1')
    C.
  • 7. Re: previously word
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    Wow!
    Excellent

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points