Case-intensive query when table case-sensitive

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Case-intensive query when table case-sensitive

Post by Shendemiar »

Can i, and how, do a case-intensive query from case-sensitive table?

Code: Select all

SELECT * FROM table_a WHERE name LIKE '%abc%' 
Returns only abc but i'd like to receive Abc and aBC as well. Any way to do this easily without altering the table?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

So "name" is a BINARY field? Interesting. You can use the string functions to convert it to lower case for the comparison.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

'name' is varchar(32) with case sensitive collation...

Would regexp be overkill? I don't know any other alternative.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

Got it

Code: Select all

SELECT * FROM `a` WHERE `name` LIKE CONVERT( _utf8 '%abc%' USING latin1 ) COLLATE latin1_general_ci
Post Reply