CODEWITHSUNDEEP
mysqlsqlregexdjangomariadb
Ivan Borshchov
Ivan Borshchov

Reputation: 3574

Django regexp in field

I have model:

class M(Model):
    re = CharacterField(max_length=50, blank=true)

For example in table I have:

table m
----------------
| id  |  re    |
----------------
|  1  |  \d+   |
|  2  |  \:abc |
----------------

I want to find some object which will match my input string (inp) by regexp stored in re field, see example:

inp = ":abc"
for m in M.objects.all():
    if re.match(m.re, inp)
        print("{} matched".format(m.id)) # 2 matched

But is it possible to perform match on DB server? So replace .all() to '.filter' with some expression?

Upvotes: 4

Views: 2065

Answers (3)

Ivan Borshchov
Ivan Borshchov

Reputation: 3574

for m in M.objects.filter().extra(where=["'{}' RLIKE `m`.`re`".format(inp)])
    print("{} matched".format(m.id))

Upvotes: 2

Rick James
Rick James

Reputation: 142453

First, \d is not handled by MySQL. Use instead [0-9] or [[:digit:]].

Second, to do the regexp in SQL, have your app construct 

'[0-9]+|:abc'

and then build that into the query.

But you probably want to anchor the regexp:

'^([0-9]+|:abc)$'

Upvotes: 2

m.antkowicz
m.antkowicz

Reputation: 13581

For the regex matching you need to use __iregex after fieldname in filter call:

    M.objects.filter(re__iregex=inp)

Take a look at official documentation to get more information

EDIT

If you want the reverse operation (to check if any regex saved on database is matching your value) you cannot use simple filter but you can define your custom Manager

class CurrentManager(models.Manager):
    def match(self, value):
        objects = super(CurrentManager, self).get_query_set().all()

        #here your code
        objects = [o for o in objects if re.match(o, value)]

        return objects

class M(Model):
    re = CharacterField(max_length=50, blank=true)
    objects = RegexManager()

#usage
matched = M.objects.match('123')

Take a look at this question also.

Upvotes: 2

Related Questions