7 Replies Latest reply: Mar 18, 2013 10:47 AM by chris227 RSS

    regexp_like

    Igor S.
      Hello folks,

      Looking for a way to make this regular expression work:
      select *
        from (select '12356/123' as pojam
                from dual)
       where regexp_like(pojam, '^([[:digit:]]{5})+\/+([[:digit:]]{3})');
      I need first 5 digits followed by a slash and then another 3 digits. Thing is it doesnt have to be 5 digits. It can be 1 or 2 or 3 etc. Same goes for the last 3 digits.

      I am looking for something like:
      select *
        from (select '12356/123' as pojam
                from dual)
       where regexp_like(pojam, '^([[:digit:]]{1-5})+\/+([[:digit:]]{1-3})');
      Best regards,
      Igor
        • 1. Re: regexp_like
          chris227
          select *
            from (select '12356/123' as pojam
                    from dual)
           where regexp_like(pojam, '^\d{1,5}/+\d{1,3}$');
          There is no need to escape the slash. /+ as you wrote means 1 or more slashes, if there must exactly be one, / is sufficient.
          \d is a shortcut (perl-syntax) for [[:digit:]].
          There is no need for grouping () here.
          Edited by: chris227 on 18.03.2013 08:18
          • 2. Re: regexp_like
            Syed Ullah
            select *
              from (select '12356/123' as pojam
                      from dual)
            where regexp_like(pojam, '^\d+\/\d+');
            • 3. Re: regexp_like
              chris227
              select *
                from (
                  select '12356/123' as pojam, 'korrekt' as test from dual union all
                  select '12356/1235' as pojam, 'too may digits on te right side' as test from dual union all
                  select '12356/123asas' as pojam, 'characters on the right side' as test from dual  
                )
              where regexp_like(pojam, '^\d+\/\d+');
              
              POJAM     TEST
              "12356/123"     "korrekt"
              "12356/1235"     "too may digits on te right side"
              "12356/123asas"     "characters on the right side"
              • 4. Re: regexp_like
                Igor S.
                 select *
                  from (select '12345/123' as pojam
                          from dual)
                 where regexp_like(pojam, '^([[:digit:]]{1,5})/+([[:digit:]]{1,3})');
                Ok it works like this but a question about +.

                I had this
                 select *
                  from (select '12345/123' as pojam
                          from dual)
                 where regexp_like(pojam, '^([[:digit:]]{1,5})+/+([[:digit:]]{1,3})');
                When it had this extra + infront of the slash it didnt work. Why?

                Best regards,
                Igor
                • 5. Re: regexp_like
                  Syed Ullah
                  This actually worked for me:
                   select *
                    from (select '12345/123' as pojam
                            from dual)
                   where regexp_like(pojam, '^([[:digit:]]{1,5})+/+([[:digit:]]{1,3})');
                  If you have '+' in front of '/', that means there can me one or more '/'. So this will have a match:
                   select *
                    from (select '12345///123' as pojam
                            from dual)
                   where regexp_like(pojam, '^([[:digit:]]{1,5})+/+([[:digit:]]{1,3})');
                  • 6. Re: regexp_like
                    chris227
                    The + means, that the group before, enclosed by the parentheses (), has to appear one ore more times.
                    So this works for me with the data given.
                    What exactly didnt work?
                    To be correct you should add add $ at the end, because you wont anything follow the 1-3 digits at the end.

                    Ah i see
                    select *
                      from (select '123456/123' as pojam
                              from dual)
                     where regexp_like(pojam, '^([[:digit:]]{1,5})+/+([[:digit:]]{1,3})');
                    
                    POJAM
                    "123456/123"
                    There is a result because 12345 meets the first occurence of the group ([[:digit:]]{1,5})+ (you could haveleft out the paranthese, no grouping needed here).
                    The 6 is valid regarding the group, soit is considered as the second appearence of the group, which is allowed due to the +.

                    Edited by: chris227 on 18.03.2013 08:51
                    • 7. Re: regexp_like
                      chris227
                      Syed Ullah wrote:
                      If you have '+' in front of '/', that means there can me one or more '/'. So this will have a match:
                      No after the slash, in front it belongs to the group before.