7 Replies Latest reply: Aug 26, 2008 5:57 AM by Aketi Jyuuzou RSS

    previously word

    656678
      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
          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
            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
              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
                regexp_replace(field,'((((.*)_)*(.*)_)*XXX)*.*','\5')
                Very nice!

                Best regards

                Maxim
                • 5. Re: previously word
                  Aketi Jyuuzou
                  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
                    cd_2
                    I would simplify that regular expression to this:
                    RegExp_Replace(str, '([^_]+)_XXX|.', '\1')
                    C.
                    • 7. Re: previously word
                      Aketi Jyuuzou
                      Wow!
                      Excellent