Bug #22315 String Function Replace Case Sensitivity Option
Submitted: 13 Sep 2006 15:44 Modified: 15 Sep 2006 19:28
Reporter: Tim Gustafson Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:4.1, Etc OS:FreeBSD (FreeBSD)
Assigned to: CPU Architecture:Any
Tags: case-sensitive, REPLACE

[13 Sep 2006 15:44] Tim Gustafson
Description:
It would be really nice if there were an option to the REPLACE string function that would allow the user to specify if the operation should be case-sensitive or not.  I have had several occasions where a user wanted to replace all cases of a word with the new word, but wound up having to do the replacement several times (once for each conceivable arrangement of letter case) to achieve the desired results.

How to repeat:
mysql> select replace('Tim Gustafson','gustafson','Blah');
+---------------------------------------------+
| replace('Tim Gustafson','gustafson','Blah') |
+---------------------------------------------+
| Tim Gustafson                               |
+---------------------------------------------+

Suggested fix:
mysql> select replace('Tim Gustafson','gustafson','Blah');
+---------------------------------------------+
| replace('Tim Gustafson','gustafson','Blah') |
+---------------------------------------------+
| Tim Blah                                    |
+---------------------------------------------+
[15 Sep 2006 8:49] Valeriy Kravchuk
Thank you for a feature request. Sorry, but I am noit sure that this feature should be implemented. Instead, the following seems more reasonable to me: second argument in REPLACE has to be searched. The search should be according to the default collation. That does not mean that the search becomes case sensitive
-- if the default collation is case insensitive, then the search is case insensitive.
[15 Sep 2006 19:28] Tim Gustafson
The only problem with that solution is that users may want, from time to time, to search-and-replace one way (case sensitive) or the other (case insensitive) on the same database without redefining the database's structure.

Truthfully, following the coallation order would work for my particular need, but I can see that having the option to switch back and fourth on a per-call basis could be useful.  Also, since REPLACE is not using indexes during it's search, I don't think that switching back and fourth on a per-call basis would be difficult.

Just my $0.02.