Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

EDIT 3: OK, forget all the complicated stuff below. My question is as easy as this: Why is the second column of the following result set 0 while all other columns are 1.

SELECT 'a' = 'á',
       INSTR('András','Andras'), 
       'András' LIKE 'Andras',
       INSTR('András','Andräs')

The database and connection is set to utf8.

END EDIT

I've got an issue with MySQL's INSTR function. I have a table 'values' with collation utf8_general_ci and a VARCHAR column 'value' containing the value 'András Schiff'. Now I perform the following queries:

> SET NAMES 'utf8' COLLATE 'utf8_general_ci'
> SELECT 'a' = 'á';
1
> SELECT * FROM values WHERE value LIKE '%Andras%'
'András'
> SELECT * FROM values WHERE INSTR(value,'Andras')
(Empty)
> SELECT * FROM values WHERE INSTR(value,'Andräs')
'András'

Can anyone explain this strange behavior? I thought LIKE '%...%' and INSTR were equivalent, the latter having the advantage that the search string may contain '%'.

Thanks

EDIT: My MySQL version is Ver 14.14 Distrib 5.1.54, for debian-linux-gnu (x86_64) using readline 6.2

EDIT 2: Another thing I noticed:

> SELECT * FROM values WHERE INSTR(value,'Andras') COLLATE 'utf8_unicode_ci'

gives an error "COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'binary'". But I don't understand why the character set should be binary.

share|improve this question

2 Answers 2

I assume that it is a bug in Mysql.

I have mysql version "mysql Ver 14.14 Distrib 5.1.67, for redhat-linux-gnu (x86_64) using readline 5.1" and it errors "COLLATION 'utf8_swedish_ci' is not valid for CHARACTER SET 'binary'".

In other server using "mysql Ver 14.14 Distrib 5.5.27, for Linux (i686) using readline 5.1" and no errors although identical databases in both.

So updating mysql could solve the problem.

share|improve this answer

Run this command for a database mydb

SHOW CREATE DATABASE mydb;

Example Output:

mysql> show create database conversationminer;
+-------------------+------------------------------------------------------------------------------+
| Database          | Create Database                                                              |
+-------------------+------------------------------------------------------------------------------+
| conversationminer | CREATE DATABASE `conversationminer` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-------------------+------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Just take note of the character set of the database.

Also, do this

SHOW CREATE TABLE values\G

Just take note of the character set of the table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.11.22.2040\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["*/ |\n+-------------------+------------------------------------------------------------------------------+\n\n1 row in set (0.00 sec)\n\nJust take note of the character set of the database.\n\nAlso, do this\n\nSHOW CREATE TABLE values\\G\n\nJust take note of the character set of the table.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}