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.