Hi Ken,

> 1) It appears that once I switch my MySQL table over from a latin
> character set to UTF-8

My understanding is that a database character set is essentially a *label* that means "My intention is to put data encoded in X character set in columns/fields of certain string datatypes."  I'm more familiar with Oracle than with MySQL, but I assume they are similar in that changing the database character set from Latin-1 to UTF-8 doesn't change any data, just how that data is labeled.  If all that data *was* UTF-8 then all is well.  If some of the data was a different character set, you still have a problem of data of mixed character sets in columns of similar datatype (a database no-no).

> 2) Is there a good/easy way to make the database agnostic about
> diacritics, so that a search for "cafe" will also find "café"
> 
> The answers to both of these may be "convert data to some normalized A-
> Z field that never displays, but I can only imagine that normalizing
> even most-Roman-characters-with-diacritics to plain ASCII-style
> characters can be daunting task.

When I hear "normalized A-Z" it strikes me as a very English-centric approach.  Which may be fine for your particular database and situation, but it tends not to scale well if at some point you find yourself having to deal with non-Roman languages.  If you are learning about character sets, might as well aim for solutions that will have a wider applicability.  ;-)

As suggested by Michael Kreyche, normalization is important, both for your database data and also in regards to user-supplied search terms.  Unlike Mr. Kreyche, I would strongly advocate for NFD, the *decomposed* normalized form.  Once both the search terms and the data are NFD, the quick-and-dirty way is to then strip out any combining characters and match on what remains.  This is not ideal, since in some languages, certain accented characters are considered to be different characters (and sort differently, too, if correctly localized) than the base, un-accented character.  However, I am guessing that will probably work fine for your purposes.

Personally, I think a search feature that would list exact matches first (i.e. terms that match before stripping out the combining characters) and then fuzzy matches (i.e. terms that didn't match the first iteration but that match after stripping out the combining characters) is better.  But also more complex to implement and perhaps over-kill in this situation.

Depending on which scripting language you are using (and how much trouble you want to go to) I may have some more (opinionated) suggestions.  If you end up coding some of this yourself, you may also want to investigate the Unicode Properties/Sub-Properties available in regular expressions.  They provide a lot of power and flexibility.

-- Michael

# Michael Doran, Systems Librarian
# University of Texas at Arlington
# 817-272-5326 office
# 817-688-1926 mobile
# [log in to unmask]
# http://rocky.uta.edu/doran/
 

> -----Original Message-----
> From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
> Ken Irwin
> Sent: Wednesday, December 16, 2009 12:26 PM
> To: [log in to unmask]
> Subject: Re: [CODE4LIB] character-sets for dummies?
> 
> Hi all -- thanks for these fabulous replies. I'm learning a lot.
> 
> Armed with a bit of new knowledge, I've done some tinkering. I think
> I've solved my original quandaries, and have opened new cans of worms.
> I have a few more specific questions:
> 
> 1) It appears that once I switch my MySQL table over from a latin
> character set to UTF-8, it is not longer case-insensitive (this makes
> sense based on what I learned from the Joel on Software post). All of
> the scripting I've done until now takes advantage of the case
> insensitivity; is there an easy way to keep this case insensitive while
> in UTF-8?
> 
> 2) Is there a good/easy way to make the database agnostic about
> diacritics, so that a search for "cafe" will also find "café"
> 
> The answers to both of these may be "convert data to some normalized A-
> Z field that never displays, but I can only imagine that normalizing
> even most-Roman-characters-with-diacritics to plain ASCII-style
> characters can be daunting task.
> 
> Any advice on these particulars?
> 
> Thanks,
> Ken