Case insensitivity for SQL LIKE when contents is latin1

Question

I attempted to apply the solution posted on How can I search (case-insensitive) in a column using LIKE wildcard?, but I'm given the error

1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

How can I apply case insensitivity to a latin1 table?

try `COLLATE latin1_general_ci` instead - that should work. – dash May 05 '13 at 19:37 — dash, May 05 '13 at 19:37
@Dash why not post this as an answer? – Ky - May 06 '13 at 17:35 — Ky -, May 06 '13 at 17:35

score 2 · Answer 1 · answered May 06 '13 at 17:36

2

SELECT  *
FROM    mytable
WHERE   mycolumn = CAST('CamelCaseWord' AS CHAR CHARACTER SET latin1) COLLATE latin1_general_ci

answered May 06 '13 at 17:36

Quassnoi

score 0 · Answer 2 · edited May 06 '13 at 17:45

0

Instead of looking for case-insensitive comparison, why not just force both sides to uppercase?

SELECT * FROM trees WHERE upper(trees.`title`) LIKE '%ELM%'

edited May 06 '13 at 17:45

Ky -

answered May 06 '13 at 03:57

James K. Lowden

I was under the impression that this only worked on Oracle (we're using MySQL) – Ky - May 06 '13 at 17:35
That an unless I'm missing something, not all UTF-8 lowercase letters have a one to one matching with an uppercase letter. – Erik Philips May 06 '13 at 17:43
@Supuhstar, you may wish to [consult your manual] (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_upper). – James K. Lowden May 13 '13 at 04:10
@Erik, That's interesting. I've never heard of a letter with more than one uppercase form. What the standard C function toupper(3) return for one? Do you have an example? – James K. Lowden May 13 '13 at 04:13
@JamesK.Lowden [Phil Haacked - Turkish i Problem](http://haacked.com/archive/2012/07/05/turkish-i-problem-and-why-you-should-care.aspx) In short, `'i' == 'I'` is `true` us-en, but `false` in tr-tr. – Erik Philips May 13 '13 at 18:52
Updating that last comment: In short, `"i".ToUpper() == "I"` is `true` us-en, but `false` in tr-tr. – Erik Philips May 13 '13 at 19:51

2 Answers2