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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

13 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.

  5. hi bobby,
    can you help me for this question.
    this is password validation.
    my requirement is 1 upper case letter,1 lower case latter,1 number,1 symbol and my pass word size is min 8 characters length.

    i worked as

    select pass from passw where regexp_like(pass,’^[a-zA-Z0-9_@#&%]{1,8}$’,’i’);

    but here some problem is there with satisfies the requirement also it will show based on length of characters.

    please help me.

    • but here some problem is there without satisfying the requirement also it will show based on length of characters.

      • Bobby says:

        Suresh,

        It might be helpful to break it up into multiple calls to regexp_like like this:

        select pass from passw
        where
        regexp_like(pass,’^.*[A-Z].*$’) and
        regexp_like(pass,’^.*[a-z].*$’) and
        regexp_like(pass,’^.*[0-9].*$’) and
        regexp_like(pass,’^.*[_@#&%].*$’) and
        regexp_like(pass,’^.{8,}$’);

        These calls check the following:

        — 1 upper case letter
        — 1 lower case letter
        — 1 number
        — 1 symbol
        — password size is min 8 characters

        Also, you don’t need the ‘i’ parameter because this makes the comparison case insensitive and you are checking for upper and lower case letters.

        – Bobby

  6. Pavan Kumar says:

    HI boby,

    IN my table one column may contains data like this

    variable=1
    abc=abc
    1=1
    variable=variable
    true=true

    I want to retrieve columns which have same data before and after ‘=’
    example. 1=1 and variable=variable.
    The equal symbol ‘=’ is always there.
    whatever data is there before ‘=’ same data should be there after ‘=’ .
    I want to retrieve such data column.

    • Bobby says:

      Thank you for your comment. I am not sure that I understand your question. It sounds like you are talking about a SQL query with a condition in the where clause that has the same thing on both sides of the equal sign. For example:

      select * from dual where 1=1;

      In most cases a query will ignore any conditions in the where clause that have the same thing on both sides of the equal sign. So the example query will be treated as if it were really:

      select * from dual;

      But, I may not really understand what you are asking.

      Bobby

  7. Anonymous says:

    select case when regexp_like(‘variable=variable’,'(^.*)=\1$’) then ‘T’ else ‘F’ end as out from dual

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.