Oracle SQL 使用 Like 和通配符

Question

我使用以下查询来查找数据库中的表名称:

SELECT  table_name
FROM    user_tables
WHERE   table_name LIKE 'APP_X_%'

我想要的结果是:

APP_X_ABC
APP_X_DEF
APP_X_GHI

我得到的结果是:

APP_XYZ
APP_X123
APP_X_ABC
APP_X_DEF
APP_X_GHI

我只需要返回 X 后带有下划线的表名。我做错了什么？

Answer 1

您需要使用ESCAPE子句:

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 '!';

<强> DBFiddle Demo

_ 被视为通配符(任何单个字符)。但您需要 _ 作为文字。