Is there any way I can write a select query with where clause, that ignores non-latin characters?
For example there is Lithuanian character Ė, obviously the equivalent in latin character would be E. So is there anyway I can write a query like this:
SELECT * FROM `table` WHERE `keyword` LIKE %E%;
And I want it to return all records that contain any of these characters: E,Ė,Ę, is there any way I can achieve this automatically with SQL (or even in PHP level)?
分享 You don't say what you've tried. You also don't say what character set (utf-8) and collation your table contains. Those are the ways you control those things by default.
You could try this:
SELECT * FROM `table` WHERE `keyword` COLLATE utf8_general_ci LIKE %E%
Or this
SELECT * FROM `table` WHERE `keyword` COLLATE utf8_lithuanian_ci LIKE %E%
I don't know if the second one will work for what you want, because I don't know Lithuanian. Are E, Ė, and Ę considered the same letter in the dictionary? If they are not, then the Lithuanian collation won't match them to each other.
If you do find that a particular collation works, you can alter your column to use that collation by default.
alter table `table`
change `keyword` `keyword` collate utf8_lithuanian_ci
That's a good idea because then you have a chance of indexes speeding your search.
分享
