7 Replies Latest reply: Dec 2, 2010 1:12 AM by MichaelS RSS

    Help in Regexp_like

    587126
      Hi All,

      I need help in regexp_like.

      Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

      create table test_regexp(str varchar2(100));

      insert into test_regexp values ('123');

      insert into test_regexp values ('456A');

      insert into test_regexp values ('987654 ');

      insert into test_regexp values ('A12 34 ZZZ ');

      insert into test_regexp values (' 743 ');

      insert into test_regexp values ('ABC');

      Expected output
      ****************

      STR
      ****
      123

      987654

      743

      Search string should have only digits and spaces, spaces can be anywhere (start, middle or end of string), search string should not have anty characters or special characters.

      Thanks in advance
      Raghu
        • 1. Re: Help in Regexp_like
          Frank Kulash
          Hi, Raghu,

          You can do it using regular expressions:
          SELECT     *
          FROM     test_regexp
          WHERE     REGEXP_LIKE ( str
                        , '^[ 0-9]+$'
                        )
          ;
          but this job can be done more efficiently without using regular expressions:
          SELECT     *
          FROM     test_regexp
          WHERE  str     IS NOT NULL
          AND    TRANSLATE ( str
                            , 'x 0123456789'
                     , 'x'
                     )  IS NULL
          ;
          Edited by: Frank Kulash on Dec 1, 2010 11:23 AM
          • 2. Re: Help in Regexp_like
            Oviwan
            this should work:
            select sysdate 
            from dual 
            where regexp_like(' 3 9234  2 ','^([[:digit:]]|[[:blank:]])+$');
            • 3. Re: Help in Regexp_like
              MichaelS
              Or
              select *
                from test_regexp
               where rtrim (str, ' 0123456789') is null
              /
              • 4. Re: Help in Regexp_like
                587126
                Hi All,

                Thanks for your valuable answers, if the space is between string then that string is not returned.

                The source data is in external table, while loading from external table it is not selecting data if space is between strings which has only digits and spaces.


                If I select from normal table, rtrim and regexp_like works fine even if space is in between.

                EX:
                ----
                insert into test_regexp values ('987 654 ');

                How can we solve the issue if space is between string.

                Thanks
                Raghu

                Edited by: Raghu on 2 Dec, 2010 10:56 AM
                • 5. Re: Help in Regexp_like
                  Nimish Garg
                  check this link

                  http://nimishgarg.blogspot.com/2010/03/oracle-function-isnumeric-check-value.html

                  pass your values in function as ISNUMERIC(replace(mycolumn,' '))

                  Nimish Garg
                  http://nimishgarg.blogspot.com
                  • 6. Re: Help in Regexp_like
                    818663
                    hi,
                    try the following:

                    select *
                    from test_regexp
                    where regexp_like(src,'^[ 0-9 ]+([ 0-9 ]+)$');


                    or
                    select *
                    from test_regexp
                    where regexp_like(src,'^([[:digit:]]|[[:blank:]]|[[:digit:]])+$');

                    Edited by: pusala on Dec 2, 2010 12:09 PM

                    Edited by: pusala on Dec 2, 2010 12:11 PM
                    • 7. Re: Help in Regexp_like
                      MichaelS
                      while loading from external table it is not selecting data if space is between strings which has only digits and spaces.
                      May be there are some unknown characters in the data as well.
                      Can you post the outcome of a row in question, like
                       select str, dump(str) from your_external_table where str_presumably_contains_digits_and_spaces
                      ?