Flask-SqlAlchemy filter full word matching

Question

I've hit a brick wall when it comes to attempting to do full word matching during a flask-sqlalchemy filter query.

Currently in my code, i'm using ilike and %+search_term+% to find items within my database, but whenever I use a term like "king" it will obviously match to thing such as 'unlocking' or 'tracking'. I would only like to match 'king' by itself, regardless of location in the string, but not when it is part of another word.

regex_var = r'\bking\b'
test_var = re.search(regex_var,'this is unlocking a king')
print(test_var)
test = database.query.filter(database.title.op('regex')(regex_var)).all()

In my above example, the regex test works totally fine and will only match the last 'king' in the test string and will fail if I remove the 'king' from the end of the test string. But whenever I try it with the database query, regardless of how I format the regex queries, be it with 'regexp_match' or 'op', it won't find anything or will throw errors. I've also tried '\\bking\\b' which fails the first test as well as the second test. I've tried removing the raw string 'r', which obviously did not help, and only having the term 'king' in there performs the same as the ilike %...% query returning results where 'king' is still a substring of 'unlocking' etc. If I remove the 'all()' at the end, it won't throw any errors, but the final query isn't iterable at all, and will throw errors the second I try to iterate or manipulate any of the contents.

Through all of my test.. the special characters like ^ or $ work fine within the regex expression, but the special sequence like '\b' will not work.**

I've tried researching wildcard flags that might be more effective for ilike % that would be more effective for the beginning and end string matching like '\b' for regex but haven't found anything thus far.

Looking for help or advice from anyone else that has come across a similar issue. I've pretty much already tried everything that I've found within all relevant posts in on this site, thus I am posting my question now because none of them have worked so far.

Answer 1

FULLTEXT(col)

MATCH(col) AGAINST("king" IN BOOLEAN MODE)

Will match words "king", "kings" and maybe a few others, but not "working".

Either king or queen (anywhere in col):

MATCH(col) AGAINST("king queen" IN BOOLEAN MODE)

Both king and queen:

MATCH(col) AGAINST("+king +queen" IN BOOLEAN MODE)

Old Regexp (for "or"):

col REGEXP "[[:<:]](king|queen)[[:>:]]"

New Regexp (for "or"):

col REGEXP "\\b(king|queen)\\b"

FULLTEXT indexing has some limitations. But when it is applicable it is much faster than LIKE or REGEXP (aka RLIKE).