78738

Which MySQL collation compares e.g. é and e as equal?

Question:

EDIT: if no such collation exists, I can work around it if MySQL has a function which will convert strings into ASCII equivalents e.g. FUNC('Være')='Vaere' -- there might be several such functions, one for each alphabet, which would be a hassle but acceptable if effective.

<hr />

I need a MySQL5 collation which will compare accented characters as equivalent, irrespective of accents. e.g. "fêter" should be considered equal to "feter" and "eł" should match "el"

Ideally this wouldn't be limited to single-character substitutions, e.g. "For At Være Som Man Bør" should be considered equal to "For At Vaere Som Man Bor". WHERE name LIKE '%ere%' should also match it.

I see there are collations for spanish/polish/etc, but I need <strong>one</strong> collation to cover <strong>all</strong> latin-like alphabets (not Arabic or Asian ones etc). The strings are stored as UTF-8.

Which collation should I use?

Answer1:

Since you said you're using the utf8 characters set, the answer to your original question is "all of them except utf8_bin".

In all of these collations you will find that 'e' = 'ê'.

However, I don't know of any collations where 'æ' = 'ae' or 'ø' = 'o'.

utf8_czech_ci utf8_danish_ci utf8_esperanto_ci utf8_estonian_ci utf8_general_ci utf8_hungarian_ci utf8_icelandic_ci utf8_latvian_ci utf8_lithuanian_ci utf8_persian_ci utf8_polish_ci utf8_roman_ci utf8_romanian_ci utf8_sinhala_ci utf8_slovak_ci utf8_slovenian_ci utf8_spanish_ci utf8_spanish2_ci utf8_swedish_ci utf8_turkish_ci utf8_unicode_ci

Answer2:

utf8-general-ci would be the closest one: <a href="http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html" rel="nofollow">http://www.collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html</a>

Also note that as of MySQL 5.0 you can create your own collations. <a href="http://dev.mysql.com/doc/refman/5.0/en/adding-collation.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/adding-collation.html</a> http://forge.mysql.com/wiki/How_to_Add_a_Collation

Recommend

  • Application error billing
  • How to securely authenticate AD user in Powershell (encrypt credentials)
  • Have perl execute shellscript & take over env vars
  • import XML to HTML
  • Creating a Countdown Watch with a slider in Xcode
  • getting cpu usage of one process using c++ (win32)
  • Python - WordNet NLTK KeyError
  • Using the sendgrid node-module, how do I send a SendGrid Template Engine email?
  • Is std::string supposed to have only Ascii characters?
  • How to create in php a search.json twitter-like
  • Class 'Twilio\\Rest\\Client' not found
  • Avoiding eval(parse()) in building fractional polynomial function
  • Filter_Key by Application
  • How can we handle large matrices in matlab(larger than 10000x10000)
  • Delphi: how to compose an email in Outlook without using MAPI?
  • How to remove special characters in file names?
  • Connection String in a WIX Custom Action Parameter
  • How to cache images only in disk using Kingfisher?
  • Accessing Redshift from Lambda - Avoiding the 0.0.0.0/0 Security Group
  • Vertical alignment of float:left div's
  • AWS S3 implementation for serving private files
  • ruby/rails: extending or including other modules
  • How to get the index of element in the List in c#
  • Rails AREL .where statement
  • How to get Fully qualified domain name in unix
  • Call a php script whenever an e-mail is received?
  • segue from landscape only to portrait only
  • Using HTML/CSS for UI in XNA?
  • C function strchr - How to calculate the position of the character?
  • Find group of records that match multiple values
  • Trying to get the char code of ENTER key
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • What is Eclipse's Declaration View used for?
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Redux, normalised entities and lodash merge
  • Do create extension work in single-user mode in postgres?
  • WOWZA + RTMP + HTML5 Playback?