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?

• Mysql Case Sensitive Query
• Disable Mysql Case Insensitive

In Oracle, primary keys are case-sensitive, so if two values differ only in case they can be used as two different primary keys. In SQL Server, by default, primary keys are case-insensitive and when you transfer data from Oracle to SQL Server and then try to create the primary key constraints, you may have duplicate key errors. A binary string is case-sensitive in comparisons. To compare the string as case insensitive, convert it to a nonbinary string and use COLLATE to name a case-insensitive collation. May 27, 2019  Using a binary collation (or the binary operator) will make string comparisons accent sensitive as well as case sensitive. What is utf8mb4? The utf8 character set in MySql is an alias for utf8mb3 which has been deprecated in recent versions because it does not support 4 byte characters (which is important for encoding strings like 🐈).

Mysql Case Sensitive Query

I'm willing to upgrade my MySQL (currently running 5.1) to get added functionality. Mysql charset utf8 collation utf8unicodeci;Charset changedmysql select 'abc' like '%B%';+-+ 'abc' like '%B%' +-+ 1 +-+mysql select replace('aAbBcC', 'a', 'f');+-+ replace('aAbBcC', 'a', 'f') +-+ fAbBcC. My 2 cents.Since many people have upgraded from MySQL to MariaDB those people will have available a new function called REGEXPREPLACE. Use it as you would a normal replace, but the pattern is a regular expression.This is a working example: UPDATE `myTable`SET `myField` = REGEXPREPLACE(`myField`, '(?i)my insensitive string', 'new string')WHERE `myField` REGEXP '(?i)my insensitive string'The option (?i) makes all the subsequent matches case insensitive (if put at the beginning of the pattern like I have then it all is insensitive).See here for more information:Edit: as of MySQL 8.0 you can now use the regexpreplace function too, see documentation.

Alternative function for one spoken by fvox. This modification of Luist's answer allows one to replace the needle with a differently cased version of the needle (two lines change). DELIMITER CREATE FUNCTION caseinsensitivereplace ( REPLACEWHERE text, REPLACETHIS text, REPLACEWITH text )RETURNS textDETERMINISTICBEGINDECLARE lastoccurency int DEFAULT '1';IF LENGTH(REPLACETHIS) 0 DOBEGINSET lastoccurency = Locate(LCASE(REPLACETHIS), LCASE(REPLACEWHERE), lastoccurency);SET REPLACEWHERE = Insert( REPLACEWHERE, lastoccurency, LENGTH(REPLACETHIS), REPLACEWITH);SET lastoccurency = lastoccurency + LENGTH(REPLACEWITH);END;END WHILE;RETURN REPLACEWHERE;END; DELIMITER.

Disable Mysql Case Insensitive

Hi, I need to search and replace a web address, but the original address could be in any case. Is there a way of doing a case insensitive search a replace?Test the position of your substring using INSTR against a LOWERedversion of the string. This gives you the position of the substring. Thenuse that within an INSERT function (not the INSERT statement) to replacethe substring.INSERT(textField, INSTR(LOWER(textField), LOWER(oldUrl)), LENGTH(oldUrl),newUrl)See INSERT and INSTR docs on this page:urlK. Test the position of your substring using INSTR against a LOWERed version of the string. This gives you the position of the substring.

Then use that within an INSERT function (not the INSERT statement) to replace the substring. INSERT(textField, INSTR(LOWER(textField), LOWER(oldUrl)), LENGTH(oldUrl), newUrl) See INSERT and INSTR docs on this page: urlthat works great, but the problem is that I might have more that oneentry in the textField.Is there a way of doing a recurring INSERT(.) in one UPDATE statement?Many thanksSimon. Hum, that works great, but the problem is that I might have more that one entry in the textField. Is there a way of doing a recurring INSERT(.) in one UPDATE statement?I don't think so. REPLACE does that, but not INSERT. You can nestthem, but that's not recurring.Not every task is best done in a single SQL statement.

You may have towrite application code.Another suggestion is that if you need to do this a single time (instead ofautomated), generate a series of UPDATE statements:SELECT CONCAT('UPDATE myTable SET textField = ',REPLACE(textField, ', '),' WHERE primaryKey = ', primaryKey, ';')FROM myTableWHERE INSTR(LOWER(textField), LOWER(oldUrl)) 0Then hand-edit the output with a text editor, which should be able to docase-insensitive global search & replace very easily. Then run the updatestatements as a script.Regards,Bill K.