Page 1 of 1

Case-intensive query when table case-sensitive

Posted: Thu Sep 13, 2007 5:10 pm
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?

Posted: Thu Sep 13, 2007 5:21 pm
by feyd
So "name" is a BINARY field? Interesting. You can use the string functions to convert it to lower case for the comparison.

Posted: Thu Sep 13, 2007 5:30 pm
by Shendemiar
'name' is varchar(32) with case sensitive collation...

Would regexp be overkill? I don't know any other alternative.

Posted: Thu Sep 13, 2007 5:39 pm
by Shendemiar
Got it

Code: Select all

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