# 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

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.

### 11 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.

• 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