Oracle SQL需求：查找包含对应人员姓名的语句记录

Hey there! Let's work through this Oracle SQL problem where you need to pull records where the PHRASE column includes either the person's FIRSTNAME or LASTNAME from the same row.

First, let's start with a straightforward approach using the LIKE operator—this is probably the most intuitive way for this scenario:

SELECT *
FROM your_table_name
WHERE PHRASE LIKE '%' || FIRSTNAME || '%'
   OR PHRASE LIKE '%' || LASTNAME || '%';

Here, we're using Oracle's string concatenation operator || to wrap the first and last names with % wildcards, which match any sequence of characters (including none). This checks if either name appears anywhere within the PHRASE text.

Handling Case Sensitivity #

One thing to watch out for is case matching. If your PHRASE has lowercase versions of the names but your FIRSTNAME/LASTNAME are capitalized (or vice versa), the above query might miss those records. To fix this, we can standardize the case with LOWER() (or UPPER() if you prefer):

SELECT *
FROM your_table_name
WHERE LOWER(PHRASE) LIKE '%' || LOWER(FIRSTNAME) || '%'
   OR LOWER(PHRASE) LIKE '%' || LOWER(LASTNAME) || '%';

Alternative: Using INSTR() #

Another option is to use Oracle's INSTR() function, which returns the position of a substring within a string (and 0 if it doesn't exist). This can be a bit cleaner in some cases:

SELECT *
FROM your_table_name
WHERE INSTR(PHRASE, FIRSTNAME) > 0
   OR INSTR(PHRASE, LASTNAME) > 0;

And again, to make it case-insensitive:

SELECT *
FROM your_table_name
WHERE INSTR(LOWER(PHRASE), LOWER(FIRSTNAME)) > 0
   OR INSTR(LOWER(PHRASE), LOWER(LASTNAME)) > 0;

Edge Case: Special Characters in Names #

If your names might include characters that act as wildcards in LIKE queries (like % or _), you'll need to escape them to avoid unexpected matches. Here's how you can handle that:

SELECT *
FROM your_table_name
WHERE PHRASE LIKE '%' || REPLACE(FIRSTNAME, '_', '\_') || '%' ESCAPE '\'
   OR PHRASE LIKE '%' || REPLACE(LASTNAME, '_', '\_') || '%' ESCAPE '\';

This replaces any underscores with escaped versions (\_) so they're treated as literal characters instead of wildcards. You can extend this to handle % as well by adding another REPLACE call: REPLACE(REPLACE(FIRSTNAME, '_', '\_'), '%', '\%').

Just remember to replace your_table_name with the actual name of your table!

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