Special characters select issues with MySQL

Question

Based on http://www.i18nqa.com/debug/utf8-debug.html I want to perform a search in my MySQL table to see if I have rows that have encoding problems.

If I run the following query :

select t.col1 from table t where t.col1 like '%Ãš%'

it will bring all the t.col1 that have 'as' characters in them.

How can I change the query to make it fetch only the rows containing '%Ãš%', and not all that contain '%as%'.

If you want case folding, but accent sensitivity, please file a request at http://bugs.mysql.com . — Rick James, Mar 14 '17 at 22:49

Hassan Farooq · Answer 1 · 2016-05-09T17:00:46.473

1

try this if you are using collation latin1_swedish_ci

select t.col1 from table t where t.col1 regexp '^[Ãš]';

edited May 09 '16 at 17:00

answered May 09 '16 at 16:33

Hassan Farooq

Note that `REGEXP` is rather lame -- it does not understand character sets, only bytes. So Hassan'd use of it happens to work. – Rick James May 09 '16 at 22:15

score 0 · Answer 2 · answered May 09 '16 at 22:13

With MySQL's collations, case-folding and accent-stripping go together.

If you want neither, use the ..._bin collation for the character set you are using.

WHERE foo LIKE '%Ãš%' COLLATE utf8_bin

Even faster would be to declare foo to be COLLATE utf8_bin instead of whatever you have. (Note: the default for utf8 is utf8_general_ci.)

2 Answers2