Re: [PHP-DB] Query Case In-sensitive
Use LIKE clause instead of = SELECT item_number FROM item WHERE item_code LIKE 'M1234' ; This will perform case-insensitive matching on all databases I used before (Including Oracle, MySQL, SQL-Server) - Original Message - From: ramki [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:46 AM Subject: Re: [PHP-DB] Query Case In-sensitive Generally in DBs, data is case sensitive. Simply you can do an uppercase or lowercase conversion in both sides. Ex : Select item number from item where upper(item_code) = upper(m1234); -ramki - Original Message - From: Larry Sandwick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 1:23 AM Subject: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
As an Oracle DBA (9+ years, versions 7.x - 9.x) I just wanted to make a correction. The LIKE command uses wild card characters ( _ and % ) for substitutions. Oracle IS a case-sensitive RDBMS and the LIKE command will NOT do case-insensitive 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: [EMAIL PROTECTED] http://usphdba.gsk.com/ - USPHARMA Database Site mustafa ocak [EMAIL PROTECTED] 19-Nov-2003 07:11 To [EMAIL PROTECTED] cc Subject Re: [PHP-DB] Query Case In-sensitive Use LIKE clause instead of = SELECT item_number FROM item WHERE item_code LIKE 'M1234' ; This will perform case-insensitive matching on all databases I used before (Including Oracle, MySQL, SQL-Server) - Original Message - From: ramki [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:46 AM Subject: Re: [PHP-DB] Query Case In-sensitive Generally in DBs, data is case sensitive. Simply you can do an uppercase or lowercase conversion in both sides. Ex : Select item number from item where upper(item_code) = upper(m1234); -ramki - Original Message - From: Larry Sandwick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 1:23 AM Subject: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
Yes you are right, I have tried the queries on Oracle and MySQL, it works right on MySQL (case insensitive) but not on Oracle. Thank you for the correction. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 2:36 PM Subject: Re: [PHP-DB] Query Case In-sensitive As an Oracle DBA (9+ years, versions 7.x - 9.x) I just wanted to make a correction. The LIKE command uses wild card characters ( _ and % ) for substitutions. Oracle IS a case-sensitive RDBMS and the LIKE command will NOT do case-insensitive 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: [EMAIL PROTECTED] -- http://usphdba.gsk.com/ - USPHARMA Database Site mustafa ocak [EMAIL PROTECTED] 19-Nov-2003 07:11 To [EMAIL PROTECTED] cc Subject Re: [PHP-DB] Query Case In-sensitive Use LIKE clause instead of = SELECT item_number FROM item WHERE item_code LIKE 'M1234' ; This will perform case-insensitive matching on all databases I used before (Including Oracle, MySQL, SQL-Server) - Original Message - From: ramki [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:46 AM Subject: Re: [PHP-DB] Query Case In-sensitive Generally in DBs, data is case sensitive. Simply you can do an uppercase or lowercase conversion in both sides. Ex : Select item number from item where upper(item_code) = upper(m1234); -ramki - Original Message - From: Larry Sandwick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 1:23 AM Subject: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
you can convert both of the sides to one case, similar to this: select item_number from item where upper (item_code) = upper (M1234); this way you ensure that both the value in the database and the selection variable are in one and the same case: capital. this should work in just about every single database, there's always a function to convert a string to upper case, the syntax might be different to the above example (which is mysql). just consult your dbms documentation. ciao, andrei . Larry Sandwick [EMAIL PROTECTED] 2003/11/17 09:53 PM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc Subject [PHP-DB] 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
[PHP-DB] 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
Re: [PHP-DB] Query Case In-sensitive
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: [EMAIL PROTECTED]
http://usphdba.gsk.com/ - USPHARMA Database Site
Larry Sandwick [EMAIL PROTECTED]
17-Nov-2003 14:53
Please respond to [EMAIL PROTECTED]
To
[EMAIL PROTECTED]
cc
Subject
[PHP-DB] 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
Re: [PHP-DB] Query Case In-sensitive
From: Larry Sandwick [EMAIL PROTECTED] Is there a way to run a query so that it ignores the case, and the query is not case sensitive? What's this have to do with PHP and what database are you using? ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
Hey! At least in Postgresql you can do something like: SELECT * FROM table where item_code ILIKE 'blabla'; But I guess it's standard, so you can use it in any DB. Larry Sandwick wrote: 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
Generally in DBs, data is case sensitive. Simply you can do an uppercase or lowercase conversion in both sides. Ex : Select item number from item where upper(item_code) = upper(m1234); -ramki - Original Message - From: Larry Sandwick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 1:23 AM Subject: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
