Oracle SQL中LIKE语句因列名NAME为保留字无法正常工作的排查

Hey there, let's break down what's going on with your query—you're right that NAME being an Oracle reserved word is a hassle, but there are two key issues with your current statement that are causing it to return no results even when the target string exists:

1. Your table name TABLE is also a reserved word (and isn't properly quoted) #

TABLE is a core Oracle keyword, so if you've actually named your table TABLE, you must wrap it in double quotes just like you did with the NAME column. Without quotes, Oracle interprets TABLE as a keyword instead of your table name, meaning your query isn't actually targeting the table you think it is. That's a critical oversight here.

2. Oracle's LIKE is case-sensitive by default #

Unless your database's NLS settings are configured to ignore case, LIKE '%apple%' will only match exact lowercase instances of "apple". If your data has mixed case (like Apple, APPLE, or ApPlE), the query won't pick those up.

Fixed Query Options #

Here's how to adjust your statement to fix both issues:

Option 1: Case-insensitive match (most reliable for broad searches)

Use UPPER() (or LOWER()) to normalize both the column value and your search string:

SELECT "TABLE"."NAME" 
FROM "TABLE" 
WHERE UPPER("TABLE"."NAME") LIKE '%APPLE%';

Option 2: Exact case match (if you're sure about the case in your data)

Just make sure to properly quote the table name:

SELECT "TABLE"."NAME" 
FROM "TABLE" 
WHERE "TABLE"."NAME" LIKE '%apple%';

Quick Best Practice Tip #

I'd strongly recommend avoiding Oracle reserved words for table and column names entirely. Having to wrap identifiers in double quotes every time you reference them is error-prone and makes your code harder to read/maintain.

内容的提问来源于stack exchange，提问作者Jaymes