MySql case sensitive?

**Michael Fesser** · Jul 14 '08, 11:25 PM

Re: MySql case sensitive?

..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.data bases.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** · Jul 14 '08, 11:45 PM

Re: MySql case sensitive?

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_na me) LIKE 'D%'. That would return both
'david' and 'Daryl'.

**Michael Fesser** · Jul 15 '08, 12:15 AM

Re: MySql case sensitive?

..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** · Jul 15 '08, 02:15 AM

Re: MySql case sensitive?

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.
jstucklex@attgl obal.net
=============== ===

**Michael Fesser** · Jul 15 '08, 02:15 AM

Re: MySql case sensitive?

..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** · Jul 15 '08, 05:45 PM

Re: MySql case sensitive?

On Jul 15, 1:44 am, Derik <ReGenes...@aol .comwrote:

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..

MySql case sensitive?

MySql case sensitive?

Comment

Comment

Comment

Comment

Comment

Comment