Strange problem with MySQL string comparison

#1

I have been using MySQL on and off for a number of years but I am
stumped by this problem.

I am doing a select which requires a join to a second table using an
account key, I have a WHERE clause which ensures that I only get the
correct type of account.
Basically the type of account is 'CUSTOMER' but if I use the code

....
WHERE ACCOUNT.TYPE="CUSTOMER"
....

Then I get no matches.
However, if I use

....
WHERE STRCMP(ACCOUNT.TYPE,"CUSTOMER")=0
....

Then I get matches as expected.

Could anyone explain why I get a result set with a STRCMP match, but
not with the equals operator?

TIA

#2

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

....
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
....

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

#3

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

....
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
....

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

#4

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

....
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
....

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

#5

"jammypodger" <rick (AT) fourfront (DOT) ltd.uk> wrote

Quote:

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. What does
MySQL doc say about testing strings of different lengths for equality?

#6

"jammypodger" <rick (AT) fourfront (DOT) ltd.uk> wrote

Quote:

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. What does
MySQL doc say about testing strings of different lengths for equality?

#7

"jammypodger" <rick (AT) fourfront (DOT) ltd.uk> wrote

Quote:

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. What does
MySQL doc say about testing strings of different lengths for equality?

#8

On Jun 5, 1:10*pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

"jammypodger" <r... (AT) fourfront (DOT) ltd.uk> wrote in message

news:eddca4b9-b026-4864-8cef-5a135a53598e (AT) e39g2000hsf (DOT) googlegroups.com...

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. *What does
MySQL doc say about testing strings of different lengths for equality?

Field length is 8 characters so no space for blanks

#9

On Jun 5, 1:10*pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

"jammypodger" <r... (AT) fourfront (DOT) ltd.uk> wrote in message

news:eddca4b9-b026-4864-8cef-5a135a53598e (AT) e39g2000hsf (DOT) googlegroups.com...

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. *What does
MySQL doc say about testing strings of different lengths for equality?

Field length is 8 characters so no space for blanks

#10

On Jun 5, 1:10*pm, "David Cressey" <cresse... (AT) verizon (DOT) net> wrote:

Quote:

"jammypodger" <r... (AT) fourfront (DOT) ltd.uk> wrote in message

news:eddca4b9-b026-4864-8cef-5a135a53598e (AT) e39g2000hsf (DOT) googlegroups.com...

I should add that the ACCOUNT.TYPE field is upper case.

Further to the above, if I use a query containing the following;-

...
WHERE LEFT(ACCOUNT.TYPE,8)="CUSTOMER"
...

Then I get all expected matches for the query.

The only thing that does not work is the direct comparison using the
equals operator.

btw. Version of MySQL is 5.0.27 running on Fedora Core 5.

It sounds like a problem with padding and/or trimming blanks. *What does
MySQL doc say about testing strings of different lengths for equality?

Field length is 8 characters so no space for blanks