mysql select distinct statement (strict version?)

Question

I'm running: MySQL Software version: 5.5.25 - Source distribution

I have a table like this:

Region | City
-----------------
quebec | montreal
_________________
quebec | montréal

The cities are written "montreal" and "montréal" with a french accent.

When I do 'select distinct City from table where Region="quebec"' it returns only

montreal

How do I get it to return both versions of montreal (with and without é)?

montreal
montréal

In other words how do I get sql to return both french and english spelling of the same word?

score 1 · Accepted Answer · answered Mar 10 '17 at 19:21

1

You can try:

SELECT DISTINCT city COLLATE UTF8_BIN AS city
FROM table 
WHERE Region="quebec"

answered Mar 10 '17 at 19:21

Dazak

thank you! this is much closer, do you know how to make it return actual words that have accents, right now any word that has an accent is returned as a series of numbers e.g: "7361696e74652d68c3a96cc3a86e652d64652d6b616d6f757261736b61 " – Robert Sinclair Mar 10 '17 at 19:26
:| In my pc I prooved and it works fine... I don't know why it returns to you numbers... maybe could it be something about codification of your database or something... but to be honest I don't have the answer to that – Dazak Mar 10 '17 at 19:29
had to add $cfg['DisplayBinaryAsHex'] = false; to phpmyadmin config file, thanks for your help! – Robert Sinclair Mar 10 '17 at 19:44
you're welcome... glad to help :) – Dazak Mar 10 '17 at 19:45

1 Answers1