Checking MySql for capital letters

ukndoit has asked for the wisdom of the Perl Monks concerning the following question:

I have to update one record using Perl and MySql, that has capital letters.

I don't know how to do that because MySQL is not case sensative on searches. So, I came up with just scanning the whole database, but how can I tell with Perl if it has ANY capital letters?

my $sth = $dbh->prepare(qq{SELECT `id`,`rec` FROM `memrecs`});
$sth->execute();
while(my ($_id,$_rec) = $sth->selectrow_array()) {
 if($_rec =~ /A .. Z/) {
   $_rec =~ tr/A-Z/a-z/g;
   $dbh->do(qq{UPDATE `memrecs` SET `rec` = ? WHERE `id` = ?}, undef, 
+"$_rec", "$_id");
 }
}
$sth->finish();
[download]

But I don't know how to just select from mysql those in rec that have any capital letters, can you help me?

Thank you very much Xav

Comment on Checking MySql for capital letters Download Code

Replies are listed 'Best First'.
Re: Checking MySql for capital letters by erroneousBollock (Curate) on Sep 25, 2007 at 05:38 UTC
You can probably go about the perl side in a much cleaner fashion (someone else will likely speak to that), but on the SQL side you can change the COLLATION to perform case-sensitive searches. Either change the COLLATION on the table or do it per-query: SELECT `id`, `rec` FROM `memrecs` WHERE `rec` COLLATE latin1_bin LIKE ?; then bind the like expression as normal. You might also try `latin_cs`. -David FYI, I found that info in about 15 seconds on Google with the query `+mysql +like +where +"case sensitive"`.	[reply] [d/l] [select]
Re: Checking MySql for capital letters by graff (Chancellor) on Sep 25, 2007 at 11:52 UTC
erroneousBollock hit it with the "COLLATE" option on the query, though for your purposes, a query like this would probably do best: `"SELECT rec,id from memrecs where rec collate latin1_bin rlike '[A-Z]' +" );` [download] Then again, MySQL does provide exactly what you need -- the "LOWER" function -- so you can skip the initial query and just do the update: `$dbh->do( "UPDATE memrec set rec=LOWER(rec)" );` [download] Or, you could just apply the LOWER() function whenever you query the table to get "rec" values (`SELECT LOWER(rec) ...`) and not worry about whether or not there are capital letters in that column of the database. A few other points you should know: `if ( $_rec =~ /A .. Z/) { $_rec =~ tr/A-Z/a-z/g; ... }` [download] That's the wrong way to check for upper case letters -- you would want `$_rec =~ /[A-Z]/` instead. Also, the "g" modifier does not apply to the tr/// operator (study the section about tr/// in perlop for details on its modifiers). But the regex match is unnecessary anyway. The two steps can be simplified to just this: `if ( $_rec =~ tr/A-Z/a-z/ ) { # tr/// returns the number of characters converted # so we get here only if there had been at least one A-Z }` [download]	[reply] [d/l] [select]
Re: Checking MySql for capital letters by andreas1234567 (Vicar) on Sep 25, 2007 at 08:12 UTC
MySQL is not case sensative on searches. Yes, that's the default option. But you can change that depending on the character set and/or the search operators: Case Sensitivity in Searches: By default, MySQL searches are not case sensitive. If you want to make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example: `col_name COLLATE latin1_general_cs LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_general_cs col_name COLLATE latin1_bin LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_bin` [download] -- Andreas	[reply] [d/l]