In Oracle you have a couple of choices.
(1) If you can ensure that the data stored is in one case you can apply a
function to the keyed in data
SELECT item_number FROM item WHERE item_code = lower('M1234');
(2) If you can not ensure data case then you can apply the function to
both side
SELECT item_number FROM item WHERE lower(item_code) =
lower('M1234');
You can use triggers on your tables to do case conversions then ensure
case in the table.
In later versions of Oracle you can apply function-based indexes to your
table to speed up these queries.
Roy A. Jones
US Pharma Database Administration
GlaxoSmithKline Inc. US Pharma IT, Shared Services
External: (919) 483-0266
Internal: 703-0266
Fax: (919) 315-6842
Office: RC2 - 2005
Email: roy.a.jones@xxxxxxx
http://usphdba.gsk.com/ - USPHARMA Database Site
"Larry Sandwick" <lgs@xxxxxxxxxxx>
17-Nov-2003 14:53
Please respond to lgs@xxxxxxxxxxx
To
php-db@xxxxxxxxxxxxx
cc
Subject
Query Case In-sensitive
Is there a way to run a query so that it ignores the case, and the query
is not case sensitive?
The data being stored is "m1234"
Query
Select item number from item where item_code = "M1234" ;
The query above should return the data above.
TIA
Larry Sandwick
Sarreid, Ltd.
Network/System Administrator
phone: (252) 291-1414 x223
fax : (252) 237-1592
