CODEWITHSUNDEEP
pythonmysqlregexsqlalchemyflask-sqlalchemy
Pav Sidhu
Pav Sidhu

Reputation: 6954

SQLAlchemy Regex with MySQL database using \b

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.

Upvotes: 0

Views: 724

Answers (1)

Pav Sidhu
Pav Sidhu

Reputation: 6954

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()

Upvotes: 1

Related Questions