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
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.
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.
I have similar requirement but tables are either ending with BKUP ,BKP or BACKUP. any idea how to implement
This should work:
select table_name
from user_tables
where
REGEXP_LIKE (table_name, ‘^TEST_(BKP|BKUP|BACKUP)$’)
order by table_name;
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.
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.