0

Our database has some terms included with different case (e.g. "apple" and "Apple").

When I try to get distinct values from the column (e.g. SELECT DISTINCT fruit FROM products) it ignores case and shows "apple" just once. (Same happens also if I use GROUP BY).

Is there a way to show all forms, without ignoring case?

We use MySQL 5.7

Máté Juhász
  • 2,197
  • 1
  • 19
  • 40
  • Check collations - there is case-independent collation somewhere. You may specify the collation in the query immediately: `SELECT DISTINCT fruit COLLATE '....' FROM products` – Akina Sep 21 '20 at 08:51
  • Check this answer : https://stackoverflow.com/a/5629121/10910692 .. maybe you'll find some hint – FanoFN Sep 21 '20 at 08:54
  • @Oyeme: yes, I could find a solution there. Thanks! – Máté Juhász Sep 21 '20 at 09:06

1 Answers1

1

Check collations - there is case-independent collation somewhere (in the table structure primarily).

You may specify the collation in the query immediately: SELECT DISTINCT fruit COLLATE '....' FROM products.

CREATE TABLE test (fruit VARCHAR(16))
SELECT 'apple' fruit UNION ALL
SELECT 'banana' UNION ALL
SELECT 'Apple';
SELECT * FROM test;


| fruit  |
| :----- |
| apple  |
| banana |
| Apple  |

SELECT DISTINCT fruit FROM test;

| fruit  |
| :----- |
| apple  |
| banana |

SELECT DISTINCT fruit COLLATE 'utf8mb4_0900_as_cs' FROM test;

| fruit COLLATE 'utf8mb4_0900_as_cs' |
| :--------------------------------- |
| apple                              |
| banana                             |
| Apple                              |

db<>fiddle here

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thanks! Unfortunately I get "SQL Error [1273] [HY000]: Unknown collation: 'utf8mb4_0900_as_cs'" – Máté Juhász Sep 21 '20 at 09:03
  • @MátéJuhász Check `CREATE TABLE tablename`, look at column and table collation. Check `SHOW COLLATION` for the collations list available. Select the collation which is available and safe. – Akina Sep 21 '20 at 09:04
  • there is no case sensitive collations defined, and unfortunately I can't make any changes to the table. – Máté Juhász Sep 21 '20 at 09:06
  • @MátéJuhász Add `CREATE TABLE` output to the question text. Check `SHOW COLLATION LIKE 'XXXXX_%'` where XXXXX is the first group before the underscore of the column collation (if it is `sql_` then take two groups) and add this into the question too. – Akina Sep 21 '20 at 09:08