in reply to Case sensitive in MySQL

If your issue is that you are storing character data and want to do case-insensitive matching on it, then you need to make sure that the data you are comparing are all in the same case. If you are doing the conversion on the Perl side, use the functions lc and uc or, if you're doing it on the database side, use the standard SQL functions LOWER and UPPER.

My experience is that you'll see vastly better performance if you store the data in all lowercase andor all uppercase, so that when you do the comparison against user data later, you don't need to call UPPER or LOWER on the actual data column, which will make the comparison much slower. This is a form of normalization.

Update: Corrected tiredness-induced conjunction error.

Replies are listed 'Best First'.
Re^2: Case sensitive in MySQL
by superfrink (Curate) on Jan 30, 2005 at 06:29 UTC
    MySQL's docs do say say "By default, string comparisons are not case sensitive". I think it's unexpected but that's how it works. trammell noted that you have to use the BINARY keyword to enable case sensitivity in MySQL.

    I want to say that if you are going to explicitly lowercase (or uppercase) your strings for comparison then you should use the same function on each string. I mean use the MySQL LOWER() function on both strings.

    If you were to mix Perl's lc function with the database's LOWER function then you could have problems with say umlauts or something. It might work but it's just cleaner to use the same function on both strings.
[reply]

In Section Seekers of Perl Wisdom