Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
-
Shendemiar
- Forum Contributor
- Posts: 404
- Joined: Thu Jan 08, 2004 8:28 am
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?
-
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