TalkPHP - View Single Post

Salathe · 04-04-2008, 12:15 PM

Looking at Sand_Devil's example of where upper(blah_field) like '%ZOO', by default (ie, case-insensitive) this wouldn't behave any differently (except, more slowly) to where blah_field like '%zoo'.

You can tell columns or whole tables to be case-sensitive by assigning an appropriate collation (ending in _cs [case-sensitive] or _bin [binary]). If you don't want to alter your table structure, you can change the collation, or declare the value as binary, within any given query.

Code:

Table: posts

 id  title
-----------
 1   Moo
 2   moo
 3   mop

Example Queries:

SELECT title FROM posts WHERE title LIKE 'mo%';
> 3 rows returned  (Moo, moo, mop)

SELECT title FROM posts WHERE title LIKE BINARY 'mo%';
> 2 rows returned (moo, mop)

The MySQL Manual has a page on case sensitivity in searches for a little more information.

04-04-2008, 12:15 PM	#4 (permalink)
Salathe Moderateur Join Date: Apr 2007 Posts: 1,393 Thanks: 5	Looking at Sand_Devil's example of `where upper(blah_field) like '%ZOO'`, by default (ie, case-insensitive) this wouldn't behave any differently (except, more slowly) to `where blah_field like '%zoo'`. You can tell columns or whole tables to be case-sensitive by assigning an appropriate collation (ending in `_cs` [case-sensitive] or `_bin` [binary]). If you don't want to alter your table structure, you can change the collation, or declare the value as binary, within any given query. Code: Table: posts id title ----------- 1 Moo 2 moo 3 mop Example Queries: SELECT title FROM posts WHERE title LIKE 'mo%'; > 3 rows returned (Moo, moo, mop) SELECT title FROM posts WHERE title LIKE BINARY 'mo%'; > 2 rows returned (moo, mop) The MySQL Manual has a page on case sensitivity in searches for a little more information.