The following query is supposed to match all comments containing ‘testa’, but it matches ‘testä’, as shown below:
mysql> select comment from document where comment='testa'; +---------+ | comment | +---------+ | testä | +---------+ 1 row in set (0.00 sec)
How to do an exact match only for the string ‘testa’, so that it doesn’t show results for its matching special character?
I think I have a solution for you. You can use
convert(column_name using latin1). Please check my code below=>
CREATE TABLE document( comment varchar(20) ); Insert INTO document SELECT 'testä'; Insert INTO document SELECT 'testa'; select comment from document where convert(comment using latin1)='testa';
if you want to use
COLLATE then MySql version should be 8.0 as Akina mentioned in the description.
Note : Please check the link dbfiddle