MySql case sensitive?

[Derik]

Okay, I THINK this is a PHP question...

I've been mucking with PHP for awhile now, but just recently I've been
poking at some ajax stuff, and I ran into something confusing; my
Queries were coming back case-sensitive.

I.e. WHERE user_name LIKE 'd%' would return "david" but not "Daryl"

That's weirding me out- I thought sql queries were case-insensitive by
default. (Admittedly, I haven't done much work with the kind of user-
input data where that'd ever be a problem.)

The equivalent mysqli query seems to be case insensitive- but I think
it's doing some sort of automatic collation thing... and my
destination server lacks mysqli.

....what's going on? Am I tripping on some sort of base-level thing I
just haven't run into before because I so seldom use LIKE, or is this
actually... odd? All the references I find say this sort of query is
supposed to be case-insensitive... :(
PHP 5.2.5

confused,
-Derik

[Michael Fesser]

..oO(Derik)

>Okay, I THINK this is a PHP question...

I beg to differ. There's not even a single line of PHP code here.

>I've been mucking with PHP for awhile now, but just recently I've been
poking at some ajax stuff, and I ran into something confusing; my
Queries were coming back case-sensitive.

I.e. WHERE user_name LIKE 'd%' would return "david" but not "Daryl"

What happens if you execute the same query on the MySQL command line?

>That's weirding me out- I thought sql queries were case-insensitive by
default. (Admittedly, I haven't done much work with the kind of user-
input data where that'd ever be a problem.)

The equivalent mysqli query seems to be case insensitive- but I think
it's doing some sort of automatic collation thing... and my
destination server lacks mysqli.

The 'i' in mysqli doesn't mean case-insensitive. It's a completely
different database interface (an 'i'mproved one). Finally it's always
the DBMS that executes the query, regardless of the used interface and
PHP version.

So either ask again in <news:comp.databases.mysqlor post some more
details (and PHP code!) about what you're trying to do. The part of the
query above doesn't tell much and works as expected here.

Micha

[sheldonlg]

Derik wrote:

Okay, I THINK this is a PHP question...

I've been mucking with PHP for awhile now, but just recently I've been
poking at some ajax stuff, and I ran into something confusing; my
Queries were coming back case-sensitive.

I.e. WHERE user_name LIKE 'd%' would return "david" but not "Daryl"

That's weirding me out- I thought sql queries were case-insensitive by
default. (Admittedly, I haven't done much work with the kind of user-
input data where that'd ever be a problem.)

The equivalent mysqli query seems to be case insensitive- but I think
it's doing some sort of automatic collation thing... and my
destination server lacks mysqli.

...what's going on? Am I tripping on some sort of base-level thing I
just haven't run into before because I so seldom use LIKE, or is this
actually... odd? All the references I find say this sort of query is
supposed to be case-insensitive... :(
PHP 5.2.5

confused,
-Derik

This is a sql, not a php question. However, to answer you, putting in
field names is case insensitive. However, "Dog" is not the same as
"dog". The like command tries to match in a case-sensitive manner. If
you want them all you could have your where clause as being something
similar to toupper(user_name) LIKE 'D%'. That would return both
'david' and 'Daryl'.

[Michael Fesser]

..oO(sheldonlg)

>This is a sql, not a php question. However, to answer you, putting in
field names is case insensitive. However, "Dog" is not the same as
"dog". The like command tries to match in a case-sensitive manner.

Nope. From the manual:

| SQL pattern matching allows you to use “_� to match any single
| character and “%� to match an arbitrary number of characters
| (including zero characters). In MySQL, SQL patterns are
| case-insensitive by default.

There are some cases where the comparison is done in a case-sensitive
way, but the OP didn't post enough details.

Micha

[Jerry Stuckle]

Michael Fesser wrote:

.oO(sheldonlg)

>This is a sql, not a php question. However, to answer you, putting in
field names is case insensitive. However, "Dog" is not the same as
"dog". The like command tries to match in a case-sensitive manner.

Nope. From the manual:

| SQL pattern matching allows you to use “_� to match any single
| character and “%� to match an arbitrary number of characters
| (including zero characters). In MySQL, SQL patterns are
| case-insensitive by default.

There are some cases where the comparison is done in a case-sensitive
way, but the OP didn't post enough details.

Micha

Yes, depending on several things, it might be case sensitive or not.

But that should be discussed in comp.databases.mysql, not here.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Michael Fesser]

..oO(Jerry Stuckle)

>Yes, depending on several things, it might be case sensitive or not.

But that should be discussed in comp.databases.mysql, not here.

ACK

Micha

[Betikci Boris]

On Jul 15, 1:44 am, Derik <[e-mail]:

Okay, I THINK this is a PHP question...

I've been mucking with PHP for awhile now, but just recently I've been
poking at some ajax stuff, and I ran into something confusing; my
Queries were coming back case-sensitive.

I.e. WHERE user_name LIKE 'd%' would return "david" but not "Daryl"

That's weirding me out- I thought sql queries were case-insensitive by
default. (Admittedly, I haven't done much work with the kind of user-
input data where that'd ever be a problem.)

The equivalent mysqli query seems to be case insensitive- but I think
it's doing some sort of automatic collation thing... and my
destination server lacks mysqli.

...what's going on? Am I tripping on some sort of base-level thing I
just haven't run into before because I so seldom use LIKE, or is this
actually... odd? All the references I find say this sort of query is
supposed to be case-insensitive... :(
PHP 5.2.5

confused,
-Derik

Use username and password columns as binary, so it would be case
sensitive..