MySQL 8 + Regex Word Boundaries

Question

I want to searching for the term 'ed' at the start or end of a word, the following SQL statement only matches a exact word match.

SELECT * FROM ul_product
where productname REGEXP '\\bed\\b'

If I do the following it gets results where ed is at the start or end of a word

SELECT * FROM ul_product
where productname REGEXP '(\\bed)|(ed\\b)'

Is this how it's supposed to work?

The description of word boundary and examples online led me to believe statement 1 would produce the results of statement 2.

I can use the statements I've created as is for my 'exact' and 'partial' matching, but is this right?

Answer 1

Regex '\\bed\\b' searches for 'ed' surrounded by word boundaries - in other words it searches for word 'ed'.

On the other end, regex: '(\\bed)|(ed\\b)' searches for either '\\bed' or 'ed\\b' (the pipe character stands for "or" in regexes). So it matches on 'ed' at the beginning of a word or at the end of a word - which seems to be what you want.

Note that the parentheses are not necessary here. You could just write this as:

where productname REGEXP '\\bed|ed\\b'