CODEWITHSUNDEEP
mysqlsql
vviston
vviston

Reputation: 183

MySQL returns bad result

I have question about SELECT FROM WHERE statement, which returns me bad result.

Here is my table called friends:

+----------+-----------+------------+--------+--------+-------+
| lastname | firstname | callprefix | phone  | region | zip   |
+----------+-----------+------------+--------+--------+-------+
| Lužný    | Bob       |        602 | 111222 | OL     | 79821 |
| Matyáš   | Bob       |        773 | 123456 | BR     | NULL  |
| Strouhal | Fido      |        300 | 343434 | ZL     | 76701 |
| Přikryl  | Tom       |        581 | 010101 | PL     | 72000 |
| Černý    | Franta    |        777 | 000999 | OL     | 79801 |
| Zavadil  | Olda      |        911 | 111311 | OL     | 79604 |
| Berka    | Standa    |        604 | 111234 | ZL     | 72801 |
| Vlcik    | BbB       |        736 | 555444 | KV     | 35210 |
+----------+-----------+------------+--------+--------+-------+

And here is my query.

SELECT * FROM friends WHERE region <= 'z';

I would expect that the rows with region ZL should be present, but they are not. Can you please tell me why?

Result is:

+----------+-----------+------------+--------+--------+-------+
| lastname | firstname | callprefix | phone  | region | zip   |
+----------+-----------+------------+--------+--------+-------+
| Lužný    | Bob       |        602 | 111222 | OL     | 79821 |
| Matyáš   | Bob       |        773 | 123456 | BR     | NULL  |
| Přikryl  | Tom       |        581 | 010101 | PL     | 72000 |
| Černý    | Franta    |        777 | 000999 | OL     | 79801 |
| Zavadil  | Olda      |        911 | 111311 | OL     | 79604 |
| Vlcik    | BbB       |        736 | 555444 | KV     | 35210 |
+----------+-----------+------------+--------+--------+-------+

When I try this query: 

SELECT * FROM friends WHERE region >= 'z';

the result contains both rows with region = 'ZL'

????

Thank you!

Upvotes: 0

Views: 99

Answers (3)

Loveless
Loveless

Reputation: 60

Have you tried SELECT * FROM friends WHERE region <= 'zl';?

From the computer's perspective, 'z' < 'zl'

Upvotes: 0

D Stanley
D Stanley

Reputation: 152521

Can you please tell me why?

If you add a record where region is Z, and sorted those rows alphabetically by region, would you expect ZL to come before or after Z? Obviously it would come after, so it does not meet your criteria.

If you want to only consider the first character, then add that to your criteria:

SELECT * FROM friends WHERE LEFT(region,1) <= 'Z';

I would also make Z explicitly a capital letter in case your database settings make it a case-sensitive search.

Upvotes: 0

Susan
Susan

Reputation: 198

Because "ZL" is greater than "Z." Z is just one character so will only return values less that Z or with the value of Z. What are you trying to achieve with this query?

Upvotes: 1

Related Questions