Case Sensitivity problem with LIKE... 1

[ChrisMacPherson]

I am having difficulty understanding my results when I enter search terms in to my LIKE statement (using PHP here).

My statement is :

"SELECT stuff
FROM table
WHERE status = 'active' AND areaInfo LIKE '%" . $searchTerms . "%'";

If I use "Golf" as $searchTerms I get 3 results
If I use "golf" as $searchTerms I get 4 results

It says in the mysql manual, that LIKE is case-insensitive. How can this be?

Any suggestions how to achieve real case-insensitivity?

Chris MacPherson

 

[r937]

could you show the 3 results that satisfy the first search, and the 4 results that satisfy the second?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ChrisMacPherson]

I will get the results in a sec, just had a thought:

I am searching for a match in a blob, is this possibly why this behaviour occurs, as a blob is case-sensitive.

Chris MacPherson

 

[r937]

... string comparisons are not case sensitive unless one of the operands is a binary string

BLOB columns are treated as binary strings (byte strings).

yes, that would explain it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ChrisMacPherson]

Cool, that's that sorted. I am going to to convert both to lowercase and then perform the search.

Do you reckon this will be the best solution to get around this? Any known remedies?

Thanks for your time.




Chris MacPherson

 

[r937]

areaInfo sounds like it should be TEXT, not BLOB

look up TEXT and you will see that while you can store your data in mixed case, searches are case-insensitive

which sounds like what you want anyway

so, no, i wouldn't lowercase the data

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[ChrisMacPherson]

Ah ha. I slightly misunderstood what I read then. Thanks a lot, I'm sure that should work as you suggest.



Chris MacPherson