How to search records with the special characters using like operator in mysql

Issue

I have a search bar with all the alphabetic characters where users can click any of them to get relevant results.

e.g if I click on "A" it will get results starting with all "Albert"

ALL A B c d e f g h i j k l m n o p q r s t u v w x y z Æ Å Ø Ö Ä 0-9 

So, MySQL query becomes LIKE "a%"

but the problem is, it also brings those records which are starting with the special character "Å", I don’t want these results. It should only appear when users click and search for them like using this LIKE "Å%"

how is that possible?

Update: My database schema and tables all are UTF-8 collation.

Solution

You can use the BINARY operator to search for a string based on its binary representation (etc. byte-by-byte rather character-by-character):

Like this (just replace with your table information)

SELECT * FROM `mytable` WHERE BINARY name LIKE 'A%'

The above searches byte-by-byte rather than character-by-character, which solves at least my similar problems. I’m using it to search through nativenames of countries and similar information.

UPDATE

For case-sensitive and UTF-8 data you can give a try to this:

SELECT name FROM `mytable` WHERE name COLLATE utf8_bin LIKE 'a%';   // returned only lower a's latin
SELECT name FROM `mytable` WHERE name COLLATE utf8_bin LIKE 'ä%';   // returned only lower ä's

Answered By – user1299518

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published