I need to create a web page listing glossary terms. The listing is paginated alphabetically (first page is for terms starting with A, second for B, etc.).
Say I have a Mysql table with just one column: 'name' which is a utf8 encoded varchar field. This table contains two records: 'oto' and 'óto' which should appear on the same page.
I would like to get all the non empty pages in my glossary with just one sql statement. Ideally the letters returned by the statement should be uppercase and non-special character (so 'O' in this case).
Unfortunately when I try the following select statement I get ('Ó', 2) instead of ('O', 2):
select upper(substring(term,1,1)), count(*) from plays_glossary_term group by lower(substring(term,1,1));
Is there a way with mysql to return O instead of Ó?