Django LIKE

Summary: in this tutorial, you’ll learn how to query data using based on pattern matching in Django which is equivalent to the LIKE operator.

We’ll use the Employee models from the HR application for the demonstration. The Employee model maps to the hr_employee table in the database:

startswith and istartswith

Sometimes, you want to check if a string starts with a substring. For example, you may want to find employees whose first name starts with Je.

To do that in SQL, you use the LIKE operator like this:

SELECT * 
FROM hr_employee
WHERE first_name LIKE 'Je%';Code language: Python (python)

The % is a wildcard that matches any number of characters. And the 'Je%' matches the strings that start with Je and are followed by zero or more characters.

To query data from Django using the LIKE operator, you use the startswith by appending it to a field name:

field_name__startswithCode language: Python (python)

For example, the following uses the filter() method to find employees whose first names start with Je:

>>> Employee.objects.filter(first_name__startswith='Je') 
SELECT "hr_employee"."id",        
       "hr_employee"."first_name",
       "hr_employee"."last_name", 
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE "hr_employee"."first_name"::text LIKE 'Je%'
 LIMIT 21
Execution time: 0.000998s [Database: default]
<QuerySet [<Employee: Jennifer Thompson>, <Employee: Jerry Cunningham>, <Employee: Jesus Reilly>, <Employee: Jessica Lewis>, <Employee: Jeanette Hendrix>, <Employee: Jeffrey Castro>, <Employee: Jessica Jackson>, <Employee: Jennifer Bender>, <Employee: Jennifer Moyer>]>Code language: Python (python)

If you want to find employees whose first names start with Je case-insensitively, you can use the istartswith:

>>> Employee.objects.filter(first_name__istartswith='je') 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE UPPER("hr_employee"."first_name"::text) LIKE UPPER('je%')
 LIMIT 21
Execution time: 0.001398s [Database: default]
<QuerySet [<Employee: Jennifer Thompson>, <Employee: Jerry Cunningham>, <Employee: Jesus Reilly>, <Employee: Jessica Lewis>, <Employee: Jeanette Hendrix>, <Employee: Jeffrey Castro>, <Employee: Jessica Jackson>, <Employee: Jennifer Bender>, <Employee: Jennifer Moyer>]>Code language: Python (python)

In this case, the __istartswith uses the uppercase version of the value for matching.

endswith and iendswith

The endswith and iendswith return True if a value ends with a substring. The endswith is equivalent to the following LIKE operator:

LIKE '%substring'Code language: Python (python)

For example, the following uses the endswith to find employees whose first names end with er:

>>> Employee.objects.filter(first_name__endswith='er')           
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE "hr_employee"."first_name"::text LIKE '%er'
 LIMIT 21
Execution time: 0.000999s [Database: default]
<QuerySet [<Employee: Jennifer Thompson>, <Employee: Tyler Briggs>, <Employee: Spencer Riggs>, <Employee: Roger Robinson>, <Employee: Hunter Boyd>, <Employee: Amber Brown>, <Employee: Tyler Coleman>, <Employee: Jennifer Bender>, <Employee: Jennifer Moyer>]>Code language: Python (python)

It returns the employees with the first names Jennifer, Tyler, Spencer, Roger, etc.

The iendswith is the case-insensitive version of the endswith. For example:

>>> Employee.objects.filter(first_name__iendswith='ER') 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE UPPER("hr_employee"."first_name"::text) LIKE UPPER('%ER')
 LIMIT 21
Execution time: 0.000999s [Database: default]
<QuerySet [<Employee: Jennifer Thompson>, <Employee: Tyler Briggs>, <Employee: Spencer Riggs>, <Employee: Roger Robinson>, <Employee: Hunter Boyd>, <Employee: Amber Brown>, <Employee: Tyler Coleman>, <Employee: Jennifer Bender>, <Employee: Jennifer Moyer>]>Code language: Python (python)

contains and icontains

The contains allows you to check if a string contains a substring. It is equivalent to the following LIKE operator:

LIKE '%substring%'Code language: Python (python)

For example, the following finds the employees whose first name contains the substring ff:

>>> Employee.objects.filter(first_name__contains='ff')  
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE "hr_employee"."first_name"::text LIKE '%ff%'
 LIMIT 21
Execution time: 0.001293s [Database: default]
<QuerySet [<Employee: Tiffany Jackson>, <Employee: Tiffany Holt>, <Employee: Jeffrey Castro>]>Code language: Python (python)

The query returns the employees with the first names Tiffany and Jeffrey.

The icontains is the case-insensitive version of the contains. So you can use the icontains to check if a string contains a substring case-insensitively:

>>> Employee.objects.filter(first_name__icontains='ff') 
SELECT "hr_employee"."id",
       "hr_employee"."first_name",
       "hr_employee"."last_name",
       "hr_employee"."contact_id",
       "hr_employee"."department_id"
  FROM "hr_employee"
 WHERE UPPER("hr_employee"."first_name"::text) LIKE UPPER('%ff%')
 LIMIT 21
Execution time: 0.002012s [Database: default]
<QuerySet [<Employee: Tiffany Jackson>, <Employee: Tiffany Holt>, <Employee: Jeffrey Castro>]>Code language: Python (python)

Summary