MariaDB REGEX with escaped characters

I am developing a photographic database where each image has a description which is fairly free-form. I want users to be able to search this database - initially I used LIKE %...% but that gives in many cases far too many matches - e.g search for cat may return category and other words. So I decided to use REGEXP with \b to match word boundaries which mostly works unless there are special characters.

In my test database I have records with the following text fragments:
taken in Victoria by
taken in Melbourne (Victoria) by
taken in Melbourne (Victoria). This is
this is a Victorian monument

If my search is
LIKE %victoria%
I get all 4 records as I expect - but that is probably not what a user wants.

If my search is
REGEXP '\\bvictoria\\b'
I get as expected the first 3 records only. So far so good - but I cannot control what my users look for - what if they want records 2 and 3 only? So I search for
REGEXP '\\(victoria\\)'
and I indeed get the two records I want. But if I then do
REGEXP '\\b\\(victoria\\)\\b'
I get nothing. And if try to allow for them looking for only the 3rd record (with a trailing . it also fails.
Any suggestions please?

You can use the | syntax.

This will attempt to the match either of the patterns.

\bvictoria\b|\(victoria\)

Wikipedia – Regular expression – POSIX extended.

Short gap solution:

• Use case insensitive collations on the table elements being searched

Medium solution:

• Use MariaDB's full text indexes

This will use WHERE MATCH(textfield) AGAINST('victoria') as a search criteria.

Partial offload solution:

SphinxSE provides capabilities of a full text search into the MariaDB syntax. Its getting a little dated as Sphinx has progressed significantly since abandoning development of SphinxSE.

Full Offload solutions:

You can get better full text search capabilities by using dedicated search engines like Sphinx, Solr, etc.