I am mikek's Blog

The personal blog of Mike Karthauser, who you may know from Brightstorm Limited or the internet.

A better like for searching

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, , , ,

3 Responses

  1. Andy Gale says:

    On PostgreSQL you can use ilike for this.

  2. Adam says:

    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

  3. iammikek says:

    hi adam, currently doing this on a postgres db via new CakePHP release.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

mikek on twitter

%d bloggers like this: