dkarp dkarp - 7 months ago 34
SQL Question

Case-insensitive REPLACE in MySQL?

MySQL runs pretty much all string comparisons under the default collation... except the

REPLACE
command. I have a case-insensitive collation and need to run a case-insensitive
REPLACE
. Is there any way to force
REPLACE
to use the current collation rather than always doing case-sensitive comparisons? I'm willing to upgrade my MySQL (currently running 5.1) to get added functionality...

mysql> charset utf8 collation utf8_unicode_ci;
Charset changed

mysql> select 'abc' like '%B%';
+------------------+
| 'abc' like '%B%' |
+------------------+
| 1 |
+------------------+

mysql> select replace('aAbBcC', 'a', 'f');
+-----------------------------+
| replace('aAbBcC', 'a', 'f') |
+-----------------------------+
| fAbBcC | <--- *NOT* 'ffbBcC'
+-----------------------------+

Answer

If replace(lower()) doesn't work, you'll need to create another function.

Comments