Search database with regular expression in Django

By: Varghese Chacko 1 year, 3 months ago

Regex and iregex are  the two field lookups that enable us to search database with regular expressions. regex is case-sensitive where as iregexis case-insensitive.

regex

The regular expression syntax is that of the database backend in use. If we use regular expressions of Python's re package, itwill lead to exceptions in many backends. In the case of SQLite, which has no built in regular expression support, this feature is provided by a (Python) user-defined REGEXP function, and the regular expression syntax is therefore that of Python’s re module.

Example:

Entry.objects.get(title__regex=r'^(An?|The) +')

SQL equivalents:

SELECT * FROM entry WHERE title REGEXP BINARY '^(An?|The) +'; -- MySQL

SELECT * FROM entry WHERE REGEXP_LIKE(title, '^(An?|The) +', 'c'); -- Oracle

SELECT * FROM entry WHERE title ~ '^(An?|The) +'; -- PostgreSQL

SELECT * FROM entry WHERE title REGEXP '^(An?|The) +'; -- SQLite

Using raw strings (e.g., r'foo' instead of 'foo') for passing in the regular expression syntax is recommended.

iregex

Case-insensitive regular expression match.

Example:

Entry.objects.get(title__iregex=r'^(an?|the) +')

SQL equivalents:

SELECT * FROM entry WHERE title REGEXP '^(an?|the) +'; -- MySQL

SELECT * FROM entry WHERE REGEXP_LIKE(title, '^(an?|the) +', 'i'); -- Oracle

SELECT * FROM entry WHERE title ~* '^(an?|the) +'; -- PostgreSQL

SELECT * FROM entry WHERE title REGEXP '(?i)^(an?|the) +'; -- SQLite