SQLAlchemy Regex with MySQL database using \b

Question

I want to do a regex search with SQLAlchemy like so:

db.session.query(Users).filter(User.name.op('regexp')(r'\bJohn\b')).all()

However although I have users in my MySQL database with the name 'John', I get no results returned.

If I remove \b from either side of John in the regex query then I get the result with users such as John Smith or just John. However the issue with having no \b on either side would mean people with Johnson in their name would also appear.

Why does using \b not return results? Thanks.

@PruthviRaj But if a user has a word before or after 'John' it would not return. — Pav Sidhu
– Pav Sidhu, Commented Oct 20, 2015 at 17:27

Pav Sidhu · Accepted Answer · 2015-10-20 17:31:17Z

1

This appears to be an issue with the way MySQL uses regex. Instead of using \b for word boundaries, [[:<:]] and [[:>:]] should be used like so:

db.session.query(Users).filter(User.name.op('regexp')(r'[[:<:]]John[[:>:]]')).all()

answered Oct 20, 2015 at 17:31

Pav Sidhu

6,97619 gold badges62 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy Regex with MySQL database using \b

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related