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
@patch72 wouldn’t lower() introduce special characters like accents? With upper() this will not happen. @fritshoogland
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
@alexnuijten @patch72 ik snap het niet? SQL> select upper(‘h��’) from dual; UPPER —– HՔEL
-
If you run this query it is like I expected. The accent is preserved.
Marcel Hoefs @MarcelHoefs
@patch72 @alexnuijten@jurgen_k Of: SELECT NLS_UPPER(‘groࠥ’, ‘NLS_SORT = XGerman’) nls_uppercase, UPPER(‘groࠥ’) uppercase FROM DUAL;
-
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 😉