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%';
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