By Patch ~ November 30th, 2012. Filed under: Oracle, PL/SQL, SQL.
When building your queries you might want to search case-insensitive. We normally use the UPPER() function to accomplish this. This is kind of weird IMHO, because you have to uppercase the value you are looking for. I was wondering why we are not using the LOWER() function. I put this question on Twitter the other day and got a lot of replies right away.
Frits Hoogland @fritshoogland
@patch72 AFAIK, old computer systems registered everything in uppercase. I know of old govmt systems. prbly people just kept on doing that.
That would be a very plausible explanation. We do a lot of things in a special way because we are used to doing it like that.
Alex Nuijten @alexnuijten
I don’t think this will happen. Using a special character like an accent on a character should keep that accent in both upper and lower case.
Jurgen Kemmelings @jurgen_k
If you run this query it is like I expected. The accent is preserved.
Marcel Hoefs @MarcelHoefs
This query will replace the ß character by SS, but this is more a translation of the text. So it’s not just a case change (to UPPER) but also removing all special characters.
One of the most funny replies is this one:
@patch72 I always used upper(lower()) … to be sure
The idea is exactly the opposite. I don’t want to type the uppercase characters in my query. It involves keeping the shift key pressed (or enabling the CAPS LOCK), so using lower(upper()) would make more sense IMHO.
There were some more comments, but you can look them up yourself using this link.
Any other ideas on why this is? Please use the comments