What is the SQL ''LIKE" equivalent on Django ORM queries?

Question

What is the equivalent of the following SQL statement in Django?

SELECT * FROM table_name WHERE string LIKE pattern;

I tried this:

result = table.objects.filter( pattern in string )

but it didn't work. How can I implement it?

score 316 · Accepted Answer · edited Jun 14 '23 at 12:16

316

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'

edited Jun 14 '23 at 12:16

Mark Ryan

3
2

answered Aug 09 '13 at 06:00

falsetru

357,413
63
732
636

32

And for case insensitive search use **__icontains** -> `result = table.objects.filter(string__icontains='pattern')` – Hitesh Garg Aug 11 '15 at 15:56
17

This answer only covers a subset of the possible patterns. It wouldn't handle a pattern like `%a%b%`. – kasperd Apr 18 '16 at 14:39
1

@kasperd, try: `result = table.objects.filter(string__contains='a').filter(string__contains='b')` – Mr. Lance E Sloan Mar 22 '19 at 13:58
4

@LS That would match `ba` which `LIKE %a%b%` would not. – kasperd Mar 22 '19 at 22:14
@falsetru What about cases like these? - https://stackoverflow.com/questions/57004942/django-orms-contains-vs-sqls-like – Underoos Jul 12 '19 at 10:34
3

This answer is incomplete for the reasons stated above. It should also include the information in @Dmitry's answer. – medley56 Aug 15 '19 at 16:39
@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. – falsetru Aug 16 '19 at 00:05
See the [answer by Petr Dlouhý below](https://stackoverflow.com/a/59972954/2810305) for a proper answer that handles all SQL LIKE patterns. – Lutz Prechelt Oct 13 '20 at 12:01
@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 – msanjay Apr 22 '22 at 10:04

score 55 · Answer 2 · edited Jun 27 '22 at 23:32

55

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

edited Jun 27 '22 at 23:32

Nils

5,612
4
34
37

answered Nov 26 '15 at 13:38

Dmitriy Kuznetsov

861
7
10

score 28 · Answer 3 · answered Jan 29 '20 at 17:55

28

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

answered Jan 29 '20 at 17:55

Petr Dlouhý

857
9
11

1

This is the proper answer to the question, unlike the accepted answer. – Lutz Prechelt Oct 13 '20 at 11:59
1

This is pretty cool, but is there any risk of user being able to pass dirty data into the query? – wihlke Feb 04 '22 at 21:02
This is the correct answer. With the answer I could never add a middle %... – juan Isaza Apr 17 '23 at 23:04

score 9 · Answer 4 · answered Apr 01 '16 at 02:45

9

result = table.objects.filter(string__icontains='pattern')

Case insensitive search for string in a field.

answered Apr 01 '16 at 02:45

Venkat Kotra

10,413
3
49
53

10

Nice, but the same answer had already been given almost three years earlier. – Mr. Lance E Sloan Mar 22 '19 at 13:53

score 4 · Answer 5 · answered Dec 15 '19 at 13:32

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.

You might want to make the replacement `.*?` to have lazy matching and not consume the whole string. — wihlke, Feb 05 '22 at 12:47

score 3 · Answer 6 · answered Jan 25 '21 at 09:34

full example : lets say we have table called DjangTable with string field name file_name and we want to create Django filter equivalent to the query that match space in the string file_name in mysql:

SELECT * FROM DjangTable WHERE file_name LIKE '% %' 
class DjangTable(UTModel):


    ...
    file_name = models.CharField(max_length=255, null=False)
    ...

in Django using python it will be :

pattern = ' ' # same as mysql LIKE '% %'
DjangTable.objects.filter(file_name__contains=pattern)

What is the SQL ''LIKE" equivalent on Django ORM queries?

6 Answers6

Linked