What does the standards say about lower/upper case? 1

[petersJazz]

In Oracle its not possible to search without case, for example
select ... where col = 'aab'
will not find col AAB in DB.
In SQL server its possible to set an global flag that text should be searched without case.
What does the standards say?

 

[angiole]

In Oracle, you can make a alpha column whatever case you require, ex:


Select Cust_ID,
Cust_Name
From Customer
Where upper(Cust_Name) like 'ABC%'

This query will change the Cust_Name column to upper case (or lower if you prefer) before evaluating the expression.

Naturally, your search expression should look for upper case as well.

If it comes in as mixed case, simply convert it to upper as well, as follows:

Select Cust_ID,
Cust_Name
From Customer
Where upper(Cust_Name) like upperusers_input)

Please note too, that if there is an index on a lookup column, applying any functions to it disables the index from being used by the optimizer.

Cheers

AA 8~)

 

[petersJazz]

As you noted, index can not be used. So if you have a standard product with different databases, Oracle and SQL Server, the customers using Oracle will have the problem that they need to search case sensitive or index can not be used.

 

[angiole]

??

AA 8~)

 

[angiole]

With a little imagination, you may do the following:

Before writing anything to the table, apply the upper() function. This will ensure that the data is always one case.

The user's input can still be mixed case because you may then apply the upper() function to the lookup value and still use the index.

Of course, you have to convert the data in the column to upper case before you implement this strategy, ( a mickey mouse fix )

Later
AA 8~)

 

[carp]

And, as of Oracle 8i, you can create a function-based index, so UPPER(whatever) WOULD use an index (provided you specify UPPER(whatever) in your index creation command).

 

[petersJazz]

Functional index looks like a good ide, the problem with putting everything in upper case in the DB is of course that upper case will be retrieved and that is not what you normaly want.

 

[Guest_imported]

Oracle has function based indexes - ie. indexes based on the result of a function.

eg. create index UpperCustName on Customer(UPPER(Cust_Name));

The optimizer can then use this index for queries such as

select * from customer where UPPER(Cust_Name) = 'ANDY'