I am using the following query to find the names of tables in my database:
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'APP_X_%'
The results that I WANT are:
APP_X_ABC
APP_X_DEF
APP_X_GHI
The results I am GETTING are:
APP_XYZ
APP_X123
APP_X_ABC
APP_X_DEF
APP_X_GHI
I need to ONLY return the table names that have the underscore after the X. What am I doing wrong?
You need to use ESCAPE clause:
You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. You can also search for the escape character itself by repeating it.
SELECT table_name
FROM user_tables
WHERE table_name LIKE 'APP!_X!_%' ESCAPE '!';
_ is treated as wildcard (any single character). But you need _ as literal.