List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 29 1999 8:02pm
Subject:Re: Case-sensitive search
View as plain text  
On Wed, 1999-09-29 10:58:31 -0400, Alex Heiphetz wrote:
> I'm having problem making search case-sensitive.
> For example, statement
> 
> SELECT phone FROM user WHERE user_name like '%Dan%'
[...]
> What should I do to have only "Dans" with capital "D" returned? 

You always should state with your question which version of MySQL
you're using, because MySQL is in steady development ...

Okay, back to your question:

The string functions in MySQL are always case sensitive, so you could
use any of the functions LOCATE, POSITION, or INSTR.  For example:
  SELECT phone FROM user WHERE POSITION('Dan' IN user_name)>0;

The pattern matching with regular expression (RLIKE or REGEXP) is
always case sensitive for all versions of MySQL except the newest
3.23.4.  For example:
  SELECT phone FROM user WHERE user_name REGEXP 'Dan';

For both the normal comparison (=) and the SQL pattern matching (LIKE) 
the behaviour depends on the fields that are involved:
  a) CHAR, VARCHAR,
     and all variants of TEXT fields do compare case insensitive.
  b) CHAR BINARY, VARCHAR BINARY
     and all variants of BLOB fields do compare case sensitive.
  If you compare a field from (a) with a field from (b), then the
  comparison will be case sensitiv (case sensitivity wins).
  See chapter "7.2.7 String types" of the MySQL Reference Manual
  and look for the statements on sorting and comparisons.

  Starting with V3.23.0 it's also possible to force a comparison into
  case sensitivity with the cast operator BINARY, independent of the
  types of involved fields.
  See chapter "7.3.7 Cast operators" of the MySQL Reference Manual.

So you also might change the type of user_name, or with V3.23.x try:
  SELECT phone FROM user WHERE BINARY user_name LIKE '%Dan%';

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Case-sensitive searchAlex Heiphetz29 Sep
  • Re: Case-sensitive searchBob Kline29 Sep
  • Re: Case-sensitive searchsasha29 Sep
  • Re: Case-sensitive searchMartin Ramsch29 Sep
  • Re: Bug in last CVSLenz Grimmer25 Jun