如何使用SQL LIKE运算符或其他方法查询NAMES表中指定字符串匹配的记录

If you need to pull records from the NAMES table that contain pairs like 'Tom Checked', 'Lucy Done', or 'Frank Ali' (with possible other words in between), here are two reliable ways to do it using standard SQL tools:

Using the LIKE Operator #

The LIKE operator paired with wildcard characters (%, which matches any sequence of characters including none) is ideal for this scenario. For each target pair, we check if the name column contains the first part, followed by any characters, then the second part. Combine these conditions with OR to capture all matching records:

SELECT * 
FROM NAMES 
WHERE 
    name LIKE '%Tom%Checked%' 
    OR name LIKE '%Lucy%Done%' 
    OR name LIKE '%Frank%Ali%';

Breakdown: #

• %Tom%Checked% matches any string that has 'Tom' anywhere, followed by any number of characters, then 'Checked' later in the string.
• Each OR clause adds another set of substrings we want to match against.

Using Regular Expressions (Database-Dependent) #

If your database (like MySQL, PostgreSQL, or SQL Server) supports regular expressions, you can write a more concise query. Here are examples for common databases:

-- MySQL/MariaDB
SELECT * 
FROM NAMES 
WHERE name REGEXP 'Tom.*Checked|Lucy.*Done|Frank.*Ali';

-- PostgreSQL
SELECT * 
FROM NAMES 
WHERE name ~ 'Tom.*Checked|Lucy.*Done|Frank.*Ali';

-- SQL Server (using LIKE with wildcard logic is still standard, but regex is available via PATINDEX)
SELECT * 
FROM NAMES 
WHERE PATINDEX('%Tom%Checked%', name) > 0 
   OR PATINDEX('%Lucy%Done%', name) > 0 
   OR PATINDEX('%Frank%Ali%', name) > 0;

Breakdown: #

• The regex Tom.*Checked matches 'Tom' followed by any characters (.*) then 'Checked'.
• The pipe | acts as an OR, letting you list all target pairs in a single expression.

Key Notes: #

• Both methods will return the exact records you’re targeting from your sample data:
  • ID 1 ('Tom User Checked') matches the first condition.
  • ID 2 ('Lucy Admin Done') matches the second.
  • ID 3 ('Frank Marry Ali') matches the third.
• If you ever need to match pairs regardless of order (e.g., 'Checked Tom'), you’d adjust conditions to check both sequences, but based on your sample data, the above queries work perfectly.

内容的提问来源于stack exchange，提问作者Anil Kumar Maurya