Django case sensitive search

Question

I have problem. I have model like this:

class Teams(models.Model):
    Name = models.CharField(max_length=200)
    Short_name = models.CharField(max_length=200, default = "-")

When I search team by short name, like "Har":

models.Teams.objects.filter(SHort_name = "Har")

I get results with "HAR" and "Har". Even if I try like this:

 models.Teams.objects.filter(SHort_name__exact = "Har"):

I get same results ("HAR" and Har") The Short_name column is in utf8mb4_general_ci format, so it should be ok and database connection has this options:

'OPTIONS': {'charset': 'utf8mb4', 'use_unicode': True},

I don't know what is wrong or how to get exact one result.

Matt S · Accepted Answer · 2019-11-21 16:42:02Z

2

Based on your question I assume you're using MySQL. When not using binary collation MySQL performs case insensitive string comparisons.

https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html

Your issue is at the database level and Django couldn't work around it without converting collations on every query. Change your collation in utf8_bin (or another *_bin) and you'll be all set:

By default, with a UTF-8 database, MySQL will use the utf8_general_ci collation. This results in all string equality comparisons being done in a case-insensitive manner. That is, "Fred" and "freD" are considered equal at the database level... If you want case-sensitive comparisons on a particular column or table, change the column or table to use the utf8_bin collation.

https://docs.djangoproject.com/en/2.2/ref/databases/#collation-settings

edited Nov 21, 2019 at 16:42

answered Nov 21, 2019 at 16:34

Matt S

15.5k6 gold badges60 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pinq- Over a year ago

This was it! Thanks!

Gabriel · Accepted Answer · 2019-11-21 21:44:05Z

-1

Use utf8bin (or utf8mb4_bin) collation in your db, this did the trick for me in the past.

Hopes this helps.

edited Nov 21, 2019 at 21:44

answered Nov 21, 2019 at 16:33

Gabriel

1,1599 silver badges15 bronze badges

Collectives™ on Stack Overflow

Django case sensitive search

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related