SQL中含收缩词短语的查询报错问题及REGEXP_LIKE解决方案咨询

Absolutely! REGEXP_LIKE is a perfect solution here—it lets you safely target contractions with apostrophes (like Doesn't) while avoiding the single quote syntax errors you're hitting. Here's how to make it work:

First: Fix the Single Quote Escape Issue #

Before diving into regex, remember that in SQL, single quotes inside a string need to be escaped by doubling them. So Doesn't becomes Doesn''t in your query string. This is non-negotiable for any SQL string that includes apostrophes, and REGEXP_LIKE follows this same rule.

Using REGEXP_LIKE to Match Contractions #

Exact Match for Doesn't #

If you need to find rows where a column exactly contains the phrase Doesn't, your query would look like this:

SELECT *
FROM your_table
WHERE REGEXP_LIKE(your_text_column, 'Doesn''t');

The regex pattern here is straightforward—it's just the escaped version of the contraction, and REGEXP_LIKE will scan the column for that exact sequence.

Flexible Match for Multiple Contractions #

If you want to match multiple contractions (like Don't, Can't, Wasn't), you can use a regex alternation (|) to group them:

SELECT *
FROM your_table
WHERE REGEXP_LIKE(your_text_column, '(Don''t|Doesn''t|Can''t|Wasn'');');

This will return any row where the column contains any of those contractions.

Pattern-Based Match for All n't Contractions #

For even more flexibility (e.g., matching any contraction ending in n't), use a regex pattern that captures the root word plus the contraction:

SELECT *
FROM your_table
WHERE REGEXP_LIKE(your_text_column, '\w+n''t');

Here, \w+ matches one or more word characters (letters/numbers) before the n''t (escaped n't), so it will catch Doesn't, Wasn't, Haven't, and so on.

Note on Database Differences #

Keep in mind that regex syntax can vary slightly between databases (e.g., Oracle uses \w while MySQL might use [[:alnum:]] for word characters). But the core approach—escaping single quotes and using REGEXP_LIKE to target the contraction pattern—works across most SQL databases that support regex functions.

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