How to use "#" symbol using a regular expression as a part of a whole word?

Question

Searching for hashtags as part of a singular word (not a portion of a word) in the content like so:

SELECT * FROM `messages` WHERE LOWER(`messages`.`content`) REGEXP '[[:<:]]#anxiety[[:>:]]'

It is not finding any records, however a search for the word "anxiety" works:

SELECT * FROM `messages` WHERE LOWER(`messages`.`content`) REGEXP '[[:<:]]anxiety[[:>:]]'

Looking to find messages like "She doesn't like thunderstorms. #anxiety #nervous."

Not looking to match parts of a word like "abc#anxiety". It should match "#anxiety" as a standalone word with a "#" before it like "I have #anxiety", "#anxiety sucks!", or "This is what #anxiety looks like.".

Answer 1

I assume that you work on MySQL 5.7 as otherwise your first query would not return matches either. See Regexp compatibility in the MySQL 8 documentation.

Having said that, your second query does not return matches because # is a non-alphanumerical character while the [[:<:]] pattern requires the character that follows to be alphanumerical. So this will never match. In fact, the mere presence of the # prefix already assures that this "anxiety" is not a part of a word match at the left side, so you should just do:

SELECT * FROM `messages` 
WHERE LOWER(`messages`.`content`) REGEXP '#anxiety[[:>:]]'

In a comment you say that abc#anxiety should not match (even though technically # already breaks a word). In that case do:

SELECT * FROM `messages` 
WHERE LOWER(`messages`.`content`) REGEXP '(^|[^a-zA-Z0-9_])#anxiety[[:>:]]'

In the character class [^a-zA-Z0-9_] add any other character that you wouldn't allow to precede #anxiety.

If your purpose is to find the word with an optional # in front of it, then use the previous regex with an additional ?:

SELECT * FROM `messages` 
WHERE LOWER(`messages`.`content`) REGEXP '(^|[^a-zA-Z0-9_])#?anxiety[[:>:]]'