Case-sensitive in "Using the In Operator"

Question

I am following the instructions of "SQL in 10 minutes" and encounter such a problem:

In its example "Using the In Operator"

SELECT prod_name, prod_price 
FROM Products 
WHERE vend_id IN ('DLL01','BRS01') 
ORDER BY prod_name;

produced the result:

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 12 inch teddy bear  |       8.99 |
| 18 inch teddy bear  |      11.99 |
| 8 inch teddy bear   |       5.99 |
| Bird bean bag toy   |       3.49 |
| Fish bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| Raggedy Ann         |       4.99 |
+---------------------+------------+

However, I tried with MySQL coming with the result:

MySQL [distributor]> select prod_name, prod_price from products where vend_id in ("DLL01", "BSR01") order by prod_name;
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Bird bean bag toy   |       3.49 |
| Fish bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| Raggedy Ann         |       4.99 |
+---------------------+------------+

I could retrieve the identical output if the ("DLL01", "BSR01") was set as lowercase:

MySQL [distributor]> select prod_name, prod_price from products where vend_id in ("dll01", "brs01") order by prod_name;
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 12 inch teddy bear  |       8.99 |
| 18 inch teddy bear  |      11.99 |
| 8 inch teddy bear   |       5.99 |
| Bird bean bag toy   |       3.49 |
| Fish bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| Raggedy Ann         |       4.99 |
+---------------------+------------+
7 rows in set (0.000 sec)

With my limited knowledge in SQL, it's hard to figure out Case-sensitive issue.

Possible duplicate of [How can I make SQL case sensitive string comparison on MySQL?](https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql) — Ken White, Aug 12 '18 at 01:21
Have you tried to upperize `vend_id` in the first place? `UPPER(vend_id) IN (...)`. — Little Santi, Aug 12 '18 at 01:34

score 1 · Accepted Answer · answered Aug 12 '18 at 03:21

1

You can set the case use lower() or upper(), so the appropriate logic would be:

SELECT prod_name, prod_price 
FROM Products 
WHERE UPPER(vend_id) IN ('DLL01', 'BRS01') 
ORDER BY prod_name;

answered Aug 12 '18 at 03:21

Gordon Linoff

1,242,037
58
646
786

Llew · Answer 2 · 2019-02-13T20:07:01.723

-2

There is a problem with the books white space characters in this example

WHERE vend_id**( problem here )**IN

Replacing the spaces with a new space works

This will work without case sensitivity

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01') ORDER BY prod_name;

This statement doesn't work despite looking like it should

edited Feb 13 '19 at 20:07

answered Feb 13 '19 at 18:52

Llew

1
2

Actually, it's not whitespace, it's a typo in "BSR01" vs "BRS01". Whitespace (unless in string literals) does not affect SQL query result. – Alexander Ryzhov Feb 13 '19 at 19:18
Yes SQL ignores white space. It’s a character in there that is causing the issue. I’ve copied it over from the book verbatim, removed the space between vend_id and IN and replaced it with a space and it works – Llew Feb 13 '19 at 19:55

Case-sensitive in "Using the In Operator"

2 Answers2