Case insensitive search in django for mysql

Question

I'm trying to do a case insensitive search for a substring within a field in my model.

My model:

class doctor(models.Model):
    docid = models.AutoField(primary_key=True, unique=True) # Need autoincrement, unique and primary
    name = models.CharField(max_length=35)
    username = models.CharField(max_length=15)
    regid = models.CharField(max_length=15, default="", blank=True)
    photo = models.CharField(
        max_length=35, default="", blank=True)
    email = models.EmailField(default="", blank=True)
    phone = models.CharField(max_length=15)
    qualifications = models.CharField(
        max_length=50, default="", blank=True)
    about = models.CharField(
        max_length=35, default="", blank=True)
    specialities = models.CharField(
        max_length=50, default="", blank=True)
    department = models.CharField(max_length=50, default="ENT", blank=True)
    fees = models.FloatField(default=300.0)
    displayfee = models.IntegerField(default=0, blank=True)
    slotrange = models.CharField(max_length=50, blank=True)
    slotdurn = models.IntegerField(default=10)
    breakrange = models.CharField(
        max_length=50, default="", blank=True)
    slotsleft = models.CharField(
        max_length=50, default="", blank=True)
    def __str__(self):
        return self.name
    def Range(self):
        return self.slotrange
    def listslots(self):
        SlotRange = self.slotrange
        SlotDurn = self.slotdurn
        startime = SlotRange.split('-')[0]
        endtime = SlotRange.split('-')[1]
        sthr, stmin = SplitTimeString(startime)
        enhr, enmin = SplitTimeString(endtime)
        print(stamptoday(sthr, stmin))
        print(stamptoday(enhr, enmin))
        startstamp = stamptoday(sthr, stmin)
        endstamp = stamptoday(enhr, enmin)
        secdurn = SlotDurn*60
        slotlist = []
        for sec in range(startstamp, endstamp, secdurn):
            enttime = sec + secdurn
            myrange = ("%s - %s" % (HumanTime(sec),
                                    HumanTime(enttime)))
            slotlist.append(myrange)
        return slotlist

Under the field 'name' in my mysql database, are two rows with values for name as 'Joel' and 'Jaffy Joel'.

When I do the search like this:

from appointments.models import customer, doctor, appointment
doctor.objects.filter(name__icontains='joel')

Output:

<QuerySet []>

But when I do:

doctor.objects.filter(name__icontains='Joel')

Output:

<QuerySet [<doctor: Joel>, <doctor: Jaffy Joel>]>

Why isnt the case insensitive search working for a lowercase search?

could you try doctor.objects.filter(name__icontains='oel') and see if you get result? — Arghya Saha, Aug 10 '18 at 15:33
this may be hepful https://stackoverflow.com/questions/5526334/what-effects-does-using-a-binary-collation-have — schrodingerscatcuriosity, Aug 10 '18 at 19:04

score 0 · Accepted Answer · answered Aug 14 '18 at 15:15

As pointed out in the django docs,

In MySQL, a database table’s “collation” setting determines whether exact comparisons are case-sensitive. This is a database setting, not a Django setting. It’s possible to configure your MySQL tables to use case-sensitive comparisons, but some trade-offs are involved. For more information about this, see the collation section in the databases documentation.

In my case, I solved it with

from django.db.models import Q,CharField
from django.db.models.functions import Lower
CharField.register_lookup(Lower, "lower")
doctor.objects.filter(Q(name__lower__contains='joel'))

The tip in this regard was provided by Matthew Pava from the django-users mailing list.

score 0 · Answer 2 · answered May 25 '23 at 10:23

You can try this if Joel's answer doesn't work:

from django.db.models.functions import Lower
from django.db.models import F

value="Joel"
doctor.objects.annotate(name_lower=Lower(F("name")).filter((name_lower__contains=value.lower()))

This way, you annotate an all-lowercase version of the column and filter it with the lowercase version of your value. It is effectively a case-insensitive comparison.

Case insensitive search in django for mysql

2 Answers2

Linked