I’ve been using LIKE for ages which is a useful way to search for part of a word or phrase in a db field.
$conditions = array( 'TABLE.field LIKE'=>'%'.$search_term.'%');
Now i didn’t realise that this wasn’t case sensitive meaning that ‘Ultra’ returned results but ‘ultra’ didn’t.
So to make it case insensitive you can do the following:
$conditions = array('UPPER(TABLE.field) LIKE'=>'%'.strtoupper($search_term).'%');
Not much different but now a useful(UPPER) addition to the sql arsenal.
Filed under: development , cakephp, mysql, postgres, sql
On PostgreSQL you can use ilike for this.
What version of MySQL did you test? From the MySQL 5.0.3 Manual.
The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:
mysql> SELECT ‘abc’ LIKE ‘ABC’;
-> 1
mysql> SELECT ‘abc’ LIKE BINARY ‘ABC’;
-> 0
hi adam, currently doing this on a postgres db via new CakePHP release.