CODEWITHSUNDEEP
sqloracle-databasecase-insensitive
A_J
A_J

Reputation: 1003

Why use uppercase on both sides for a case insensitive comparison in sql query in oracle?

Why use UPPER or LOWER function on both sides of '=' when doing a case-insensitive comparison in SQL query in Oracle?

E.g.
SELECT * FROM emp WHERE LOWER(empName) = LOWER('John');

Why is it necessary to write LOWER() with "John" in the above code?

What problems may occur with this ?

SELECT * FROM emp WHERE LOWER(empName) = 'john';

Kindly tell.

Upvotes: 1

Views: 2067

Answers (2)

Michael Rountree
Michael Rountree

Reputation: 325

Most databases contain standard settings on both equality and sort for the session.Various combinations will deal with scenarios of different case, diacritic marks, even languages that have a few differences in character set (ß in German can be compared to SS). For Oracle specifically, for simply case insensitive, you can run Alter Session SET NLS_COMP='BINARY_CI'`

where BINARY_CI is binary comparison and case insensitive. The reason someone would compare lower case is because they don't fully understand that the underlying algorithms of the database have led to drastically different comparisons in their past, and they don't know why. So basically, it's written for if you don't know what your doing.

Upvotes: 0

JustAPup
JustAPup

Reputation: 1780

There's no difference in the example you provided. However, consider the case when you use user's input to compare your empName with (i.e.,parameterized query). Do you trust that the user will put in 'john' and not 'John' or 'JOHN' or 'jOhN' or 'something else'? If not, you need to wrap the input with LOWER

Upvotes: 4

Related Questions