如何实现SQL反向LIKE匹配：用表中pattern字段匹配用户输入长字符串

Got it, let's work through this reverse matching problem you're tackling!

The core trick here is flipping the direction of the LIKE operator. Instead of checking if the pattern field contains your input, we’ll check if your input string contains any of the pattern values stored in the table.

The Working SQL Query #

Here’s the exact query that will return the correct result for your example:

SELECT answer
FROM answer_table
WHERE LOWER('Hello, are you there?') LIKE CONCAT('%', LOWER(pattern), '%');

What’s Going On Here? #

• CONCAT('%', LOWER(pattern), '%') converts each stored pattern into a wildcard match string (for example, the hello pattern becomes %hello%).
• Wrapping both the input string and pattern in LOWER() makes the match case-insensitive—so your input "Hello" will correctly match the lowercase "hello" pattern in the table. If you need strict case-sensitive matching, just remove both LOWER() calls.
• When you run this with your sample input, it scans every row in answer_table and returns the answer where the input string includes the row’s pattern—which in your case is "Hello there!".

For Dynamic Application Usage #

If you’re using this in an app (not a one-off query), always use parameterized queries instead of directly inserting user input into the SQL string. This stops SQL injection attacks. For example, in Python with psycopg2:

user_input = "Hello, are you there?"
query = "SELECT answer FROM answer_table WHERE LOWER(%s) LIKE CONCAT('%', LOWER(pattern), '%');"
cursor.execute(query, (user_input,))

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