REGEXP_LIKE Example

This is simple, but fun.  I have studied the regular expression features in Oracle but haven’t really used them.  I needed a list of all the tables in a particular schema that started with a certain string and ended with three or four numbers but there were a number of other tables with similar patterns in the same schema – backups, etc.  I’ve done this before with LIKE and  % and _.  But I went to review the LIKE command in the manual and ran across REGEXP_LIKE and decided to try it.  It worked pretty well.  Easier to specify the same thing than with LIKE I think.  I ended up with a condition that would work with two or more numbers so it wasn’t exactly 3 or 4 numbers but all the tables I was looking for and none of the ones I didn’t need fit the regular expression.  I built a simple test case in my own test schema that mirrored the situation  on our real system and here is the resulting query and its output:

select table_name
from user_tables
where 
REGEXP_LIKE (table_name, '^TEST_[0-9][0-9]*[0-9]$')
order by table_name;

TABLE_NAME
------------------------------
TEST_123
TEST_4567

I had to have the ^ at the beginning and the $ at the end to indicate that the regular expression matched the entire table name. [0-9] matches one digit and  [0-9]* matches zero or more digits.  The _ in this example is just the literal character _ and doesn’t match anything else.

Here is a zip of my test script and its log.

- Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to REGEXP_LIKE Example

  1. Corey Huinker says:

    You are seeking the {m,n} regex feature. It will match a minimum of m items and a max of n items.


    > create table test_123 (x number)
    table TEST_123 created.
    > create table test_1 (x number)
    table TEST_1 created.
    > create table test_12 (x number)
    table TEST_12 created.
    > create table test_1234 (x number)
    table TEST_1234 created.
    > select ut.table_name
    from user_tables ut
    where regexp_like(ut.table_name,'^TEST_[0-9]{1,3}$')
    TABLE_NAME
    ------------------------------
    TEST_123
    TEST_1
    TEST_12

    As you can see, it matched tables with 1 to 3 digits after TEST_ but not four.

    • Bobby says:

      That’s very nice. I don’t think I’ve ever seen the {m,n} syntax before, or if I have I’ve forgotten it. Thank you for your helpful comment.

  2. Anonymous says:

    I have similar requirement but tables are either ending with BKUP ,BKP or BACKUP. any idea how to implement

    • Bobby says:

      This should work:

      select table_name
      from user_tables
      where
      REGEXP_LIKE (table_name, ‘^TEST_(BKP|BKUP|BACKUP)$’)
      order by table_name;

  3. Anonymous says:

    I want to use it in forms.I want such syntax which will accept only characters(a-z) (A-Z) and spaces in text field.and another syntax which will accept only number.Could u please help me.

  4. Bobby says:

    Would this work for you?

    SQL> select ‘Matches’ from dual where
    2 regexp_like(‘asdfasd sadf adsf’,’^[a-zA-Z ]+$’);

    ‘MATCHE
    ——-
    Matches

    SQL>
    SQL> select ‘Matches’ from dual where
    2 regexp_like(’123′,’^[a-zA-Z ]+$’);

    no rows selected

    SQL>
    SQL> select ‘Matches’ from dual where
    2 regexp_like(’123′,’^[0-9]+$’);

    ‘MATCHE
    ——-
    Matches

    SQL>
    SQL> select ‘Matches’ from dual where
    2 regexp_like(’123.34′,’^[0-9]+$’);

    no rows selected

    SQL>
    SQL> select ‘Matches’ from dual where
    2 regexp_like(’123.34′,’^[0-9]+\.[0-9]+$’);

    ‘MATCHE
    ——-
    Matches

    You might also try googling “POSIX regular expressions” for more examples.

Leave a Reply