Django filter - iregex to match complete word only

Question

So I've been trying to implement a search feature where by a user can enter a keyword and look up users. The search method is supposed to look up exact word matches within the fields of the users e.g first name, last name, job etc

I have tried icontains but that would also match within the words e.g if the user enters 'a' the search would return anything that contains an 'a'. (This was what I was originally doing before I decided to fix the issue)

I tried iexact but that would obviously give an exact match and if the user was to enter 'john' it would skip the user whose first name was 'john doe' or something.

So after quite a bit of googling I found the two stackoverflow threads

django filter icontains match whole words only

Whole-word match only in Django query

Since I am using postgresql I switched to using \y instead of \b. Now the query I am currently using is

results = User.objects.filter(first_name__iregex=r"\y{0}\y".format(first))

I have also tried, and the other methods listed in the two threads I mentioned.

results = User.objects.filter(first_name__iregex=r"\y%s\y"%first)

The problem is that it doesn't return anything. While debugging I have gone and checked what query is being generated, as far as I know it's seems correct.

WHERE `LinkedApp_user`.`first_name` REGEXP \yahsan\y 

My database currently has about 5 users with one of them having the first name "ahsan" while another having first name "ahsan saleem". However this query returns nothing. I would really appreciate any help.

EDIT: I tried to switch to SQLite and change \y with \b and the search seems to have started working. However I can't work with sqlite, I would really appreciate if someone could guide me.

Another EDIT: I have solved my issue with postgresql by switching db. A stupid mistake anyway. The question is how would you make this work with mysql, all I have tried has failed.

Answer 1

In MySQL the word boundaries are matched by [[:<:]] and [[:>:]] (http://dev.mysql.com/doc/refman/5.7/en/regexp.html)