QuerySet database-side pattern regexp support

[Alex Damian]

Currently there is no API to match a string against regex patterns stored in a database. Proposing an API to enable this functionality.

Current regex API

The current set of QuerySet APIs supports regexp functions (i.e __regex and __iregex) support passing in a pattern that is matched against a field in the database. E.g. for a database value_field=CharField() containing the foo, bar and for, the queryset API Q(value__regexp='.o.') will match foo and for through a value_field REGEX 'o' WHERE clause.

New API proposal

What is missing is an API to allow the regex (proposing __revregex and __irevregex) pattern to be stored in the field and to apply a WHERE clause on a string that returns pattern-matching entries in the database.

E.G have the database field pattern=CharField() that stores a regex pattern, with the values '.o.' and '.*'.
The queryset statement Q(pattern__revregex='foo') will match both values, while the pattern Q(pattern__revregex)='bar') will match only the .* value.

Example

Currently I'm implementing this with an extra() query:

class DisplayClassifiers(models.Model):
    regex_pattern = models.CharField(max_length=10, null=False)

....

 # return my classifiers that match the string supplied

DisplayClassifiers.objects.extra(where=["'{0}' REGEXP regex_pattern".format(regex_pattern.replace("'", ""))])

This generates this SQL:

 SELECT .... FROM `mainapp_displayclassifier` WHERE ('foo-bar-go' REGEXP regex_pattern)

Works as expected with mariadb 10.0.31

[Simon Charette]

I'm not sure if this warrants inclusion as a core lookup, this seems like a rare use case and best suited for a third-party RegexField that implements this special lookup and pattern validation?

[Adam Johnson]

This can also be done with regex database functions [like those provided by Django-MySQL](​https://django-mysql.readthedocs.io/en/latest/database_functions.html#regexp-functions). Not sure there's enough similarity across DB's to warrant a cross-DB regex function, although I guess we already have the lookup.