Re: How to achieve "accent-insensitive" searching

Christoffer Vig wrote:

>Have you tried the soundex function?
>
>|SOUNDEX(/|str|/)|
>
Doesn't seem to help:

C:\Temp>mysql -u root -Nse "SELECT SOUNDEX('e')"
E000

C:\Temp>mysql -u root -Nse "SELECT SOUNDEX('é')"
é000

I now have exactly the same problem comparing 'E000' with 'é000'.

Having looked into this a little more myself, I believe that whether or 
not 'e' is considered like 'é' is actually down to the collation being 
used.  The default character set is latin1, for which the default 
collation is latin1_swedish_ci (!), in which 'e' and 'é' are actually 
considered alike:

C:\Temp>mysql -u root -Nse "SELECT 'e' COLLATE latin1_swedish_ci LIKE 
'é' COLLATE latin1_swedish_ci"
1

If you change to latin1_general_ci then they are no longer considered alike:

C:\Temp>mysql -u root -Nse "SELECT 'e' COLLATE latin1_general_ci LIKE 
'é' COLLATE latin1_general_ci"
0

I'm working with the utf8 character set, for which the default collation 
is utf8_general_ci, and it turns out that this collation considers 'e' 
and 'é' to be alike anyway (not that é is é in UTF-8):

C:\Temp>mysql -u root -Nse "SELECT _utf8'e' COLLATE utf8_general_ci LIKE 
_utf8'é' COLLATE utf8_general_ci"
1

However, there is one more thing that I still can't do:  I want to be 
able to control case-sensitivity, whilst retaining 
"accent-insensitivity".  As the name suggests, utf8_general_ci is 
case-insensitive.  Case-sensitivity can be enabled by the use of the 
BINARY cast operator:

C:\Temp>mysql -u root -Nse "SELECT _utf8'e' COLLATE utf8_general_ci LIKE 
_utf8'E' COLLATE utf8_general_ci"
1

C:\Temp>mysql -u root -Nse "SELECT BINARY _utf8'e' COLLATE 
utf8_general_ci LIKE BINARY _utf8'E' COLLATE utf8_general_ci"
0

However, the BINARY cast operator also disables the "accent-insensitivity":

C:\Temp>mysql -u root -Nse "SELECT BINARY _utf8'e' COLLATE 
utf8_general_ci LIKE BINARY _utf8'é' COLLATE utf8_general_ci"
0

Is there any way that I can do comparisons without regard to accents but 
with regard to case, so that 'e' is considered like 'é', but not like 
'E' or 'É'?

I had a look at the other utf8 collations available (show collation like 
'utf8%'), but couldn't see anything that would do this.

- Steve


------------------------------------------------
Radan Computational Ltd.

The information contained in this message and any files transmitted with it are confidential and intended for the addressee(s) only.  If you have received this message in error or there are any problems, please notify the sender immediately.  The unauthorized use, disclosure, copying or alteration of this message is strictly forbidden.  Note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Radan Computational Ltd.  The recipient(s) of this message should check it and any attached files for viruses: Radan Computational will accept no liability for any damage caused by any virus transmitted by this email.