MySQL search query, collation and case sensitivity.

I was working on mysql database and was creating a search system module for a big mysql database. Everything was going fine as usual, query was working, no bugs, database was responding. However, the main problem that I notice was case sensitivity search. My searching query was case sensitive. Although I used LIKE ‘serachquery%’ in my code but the case sensitive search cannot be prevented. If you are having this problems then there are some solutions.

The first thing is you need to know about the database collation. What is collation? shortly ‘A collation is a set of rules for comparing characters in a character set’. If you want to know briefly about collation then you should look at mysql documentation about collation, encoding and character set.

There are three type of case sensitiveness in mysql database collation.

  • ‘_bi’ -> This means binary case sensitive
  • ‘_cs’-> This means case sensitive
  • ‘_ci’ -> This means case insensitive

Solutions:

1. First, look at your database and see what type of collation did you assigned for your table entity?? If it is binary case sensitive AKA ‘_bin’ or case sensitive AKA ‘_cs’ then your normal LIKE ‘searchquery%’ won’t work. So the first way is if it possible to change the collation type.

2. The second way is much easier and code related. the code can be something like this.

SELECT tableEntity FROM table_name WHERE UPPER(tableEntity) LIKE UPPER('search_query%');

The logic for this query is whatever search query is, we are making the query in upper case using UPPER() function of mysql and we are making the result of our tableEntity in uppercase as well.

Hope those solutions will help you. Happy coding! 🙂

Advertisements

About Mazharul Anwar

A PHP and javascript nerd believe in web applications and open source goodness. Ex MS Windows user converted to linux fanboy and love gadgets. Coding in python and maintaining servers become my passion. Website performance and optimization geek.
This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

3 Responses to MySQL search query, collation and case sensitivity.

  1. Pingback: Twitted by mazharul_anwar

  2. jerng says:

    Looks like you’re doing well. Keep it up!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s