sql "LIKE" equivalent in django query
Solution 1
Use __contains
or __icontains
(case-insensitive):
result = table.objects.filter(string__contains='pattern')
The SQL equivalent is
SELECT ... WHERE string LIKE '%pattern%';
@Dmitri's answer below covers patterns like 'pattern%' or '%pattern'
Solution 2
contains and icontains mentioned by falsetru make queries like SELECT ... WHERE headline LIKE '%pattern%
Along with them, you might need these ones with similar behavior: startswith, istartswith, endswith, iendswith
making
SELECT ... WHERE headline LIKE 'pattern%
or
SELECT ... WHERE headline LIKE '%pattern
Solution 3
This can be done with Django's custom lookups. I have made the lookup into a Django-like-lookup application. After installing it the __like
lookup with the %
and _
wildcards will be enabled.
All the necessary code in the application is:
from django.db.models import Lookup
from django.db.models.fields import Field
@Field.register_lookup
class Like(Lookup):
lookup_name = 'like'
def as_sql(self, compiler, connection):
lhs, lhs_params = self.process_lhs(compiler, connection)
rhs, rhs_params = self.process_rhs(compiler, connection)
params = lhs_params + rhs_params
return '%s LIKE %s' % (lhs, rhs), params
Solution 4
result = table.objects.filter(string__icontains='pattern')
Case insensitive search for string in a field.
Solution 5
In order to preserve the order of the words as in the sql LIKE '%pattern%' statement I use iregex, for example:
qs = table.objects.filter(string__iregex=pattern.replace(' ', '.*'))
string methods are immutable so your pattern variable will not change and with .* you'll be looking for 0 or more occurrences of any character but break lines.
By using the following to iterate over the pattern words:
qs = table.objects
for word in pattern.split(' '):
qs = qs.filter(string__icontains=word)
the order of the words in your pattern will not be preserved, for some people that could work but in the case of trying to mimic the sql like statement I'll use the first option.
Aswin Murugesh
Head of Engineering at Ongil Pvt Ltd contact me : facebook Twitter mail: [email protected]
Updated on April 23, 2022Comments
-
Aswin Murugesh about 2 years
What is the equivalent of this SQL statement in django?
SELECT * FROM table_name WHERE string LIKE pattern;
How do I implement this in django? I tried
result = table.objects.filter( pattern in string )
But that did not work. How do i implement this?
-
Hitesh Garg almost 9 yearsAnd for case insensitive search use __icontains ->
result = table.objects.filter(string__icontains='pattern')
-
kasperd about 8 yearsThis answer only covers a subset of the possible patterns. It wouldn't handle a pattern like
%a%b%
. -
Mr. Lance E Sloan about 5 yearsNice, but the same answer had already been given almost three years earlier.
-
Mr. Lance E Sloan about 5 years@kasperd, try:
result = table.objects.filter(string__contains='a').filter(string__contains='b')
-
kasperd about 5 years@LS That would match
ba
whichLIKE %a%b%
would not. -
Underoos almost 5 years@falsetru What about cases like these? - stackoverflow.com/questions/57004942/…
-
medley56 almost 5 yearsThis answer is incomplete for the reasons stated above. It should also include the information in @Dmitry's answer.
-
falsetru almost 5 years@medley56, OP mentioned
pattern in string
which correspond to__contains
. And I thought people will follow the link in the answer, they can find another field lookup options. As Dmitriy already answered, I'll leave my answer as is. Thank you for your feedback. -
Lutz Prechelt over 3 yearsThis is the proper answer to the question, unlike the accepted answer.
-
Lutz Prechelt over 3 yearsSee the answer by Petr Dlouhý below for a proper answer that handles all SQL LIKE patterns.
-
wihlke over 2 yearsThis is pretty cool, but is there any risk of user being able to pass dirty data into the query?
-
wihlke over 2 yearsYou might want to make the replacement
.*?
to have lazy matching and not consume the whole string. -
msanjay about 2 years@falsetru The OP's question is where string like pattern, and the pattern can be anything. So I spent quite a bit of time putting in a % in the pattern and wondering why it didn't work till I read Dmitri's answer. So I suggest its worth mentioning the point here. Thanks