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