Menu
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' +-----------------------------+ 
287
Definition and Usage The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: This function performs a case-sensitive replacement.
Note: The REPLACE function is case-insensitive in MS SQL Server.
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
select * from users where lower(first_name) = 'ajay'; The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.
If replace(lower()) doesn't work, you'll need to create another function.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
