CODEWITHSUNDEEP
sqlsql-serveroracle-database
Upendra
Upendra

Reputation: 726

How do I avoid functions like UPPER in where clause

It is recommended that functions in the where clause should not be used. But for the following query, how do I avoid that?

SELECT empID from EmployeeTable
   WHERE UPPER(FirstName) = UPPER(LastName)

Yes, this example is overly simplified, but I am talking about joining different tables and have no control over casing of the data. I just need to write it where it is not case sensitive.

Edit: I actually need solutions for both Oracle and SQL Server.

Upvotes: 1

Views: 4826

Answers (3)

Art
Art

Reputation: 5782

You cannot avoid UPPER/LOWER in mixed case data. You can create FUNCTION BASED INDEXES in Oracle to improve the performance:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1161

http://www.oracle.com/pls/db111/to_URL?remark=ranked&urlname=http:%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FB28359_01%2Fserver.111%2Fb28310%2Findexes003.htm%23ADMIN11730

Upvotes: 3

APC
APC

Reputation: 146239

"It is recommended that functions in the where clause should not be used"

This recommendation only applies when:

  1. The columns are indexed; and
  2. We want to use the index in our search.

You haven't provided details of the relevant indexes and you say the code example is "overly simplified", so obviously we can't give you proper advice. But, there are no other criteria in the WHERE clause so, with a case-insensitive index on (lastname, firstname), the best you could hope for from the statement you posted is a Full Fast Scan on the index.

Although depending on the ratio of hits to total rows you might still get a faster search from a Full Table Scan. In that case you wouldn't want to use a index anyway, so the posted query would be fine.

This advice applies to Oracle; I don't know enough about performance on SQL Server to say what would work best on that platform. The key point is that query-optimization is all about the specifics. There are exceptions or caveats for pretty much every "recommendation" you care to mention.

Upvotes: 5

juergen d
juergen d

Reputation: 204766

Use collate to define a case-insensitive collation

SELECT empID from EmployeeTable
WHERE FirstName = LastName collate Latin1_General_100_CI_AS

SQLFiddle demo

Upvotes: 0

Related Questions