SQL搜索字符串中的＃字符问题

Question

I use some Regexp in my mySQL search queries and it seems to work fine unless I have a # character in the query.

The Regexp matches based on word boundaries because the field in which this query is searching is entire resumes / curriculum-vitaes stored in the SQL database.

For instance this works as expected and returns the correct number of results:

SELECT COUNT(*) n FROM candidate c WHERE (c.CV REGEXP '[[:<:]]java[[:>:]]');

However, this doesn't and returns 0 results when it should return a few hundred:

SELECT COUNT(*) n FROM candidate c WHERE (c.CV REGEXP '[[:<:]]c#[[:>:]]');

I understand now that this is because I am matching based on word boundaries and # cannot be the end of the word. Interestingly, "C++" works fine though.

Is there a way of modifying this REGEXP so it also works with a string like "c#"?

Answer 1

You might be able to use something like this:

SELECT 'c#' REGEXP '(^|[^a-zA-Z0-9_])c#($|[^a-zA-Z0-9_])'
SELECT 'java' REGEXP '(^|[^a-zA-Z0-9_])java($|[^a-zA-Z0-9_])'

In newer MySQL versions (8.0.4+) which support regex through ICU instead of Henry Spencer's implementation you can use \w which looks a bit cleaner:

SELECT 'c#' REGEXP '(^|[^\w])c#($|[^\w])'
SELECT 'java' REGEXP '(^|[^\w])java($|[^\w])'