Re: Changing a column to proper case

UPDATE species SET species_name = CONCAT( UCASE(LEFT(species_name, 1)), RIGHT(species_name, LENGTH(species_name)-1) ); Should work, but didn't tested. Tyrael On Wed, Apr 7, 2010 at 8:12 AM, Bill Mudry <billmudry@rogers.com> wrote: > Tyrael also suggested that and it worked! :-) . > That sure beats manually going through 6,500 records! > > Now I need a statement (UPDATE or SELECT SET?) to permanently correct all > entries in the > species_name column. I tried both but do not have my syntax quite right. > > Bill > > > At 02:08 AM 07/04/2010, you wrote: > >> > I have a MySQL file that is filled with information on the different >> > woods of the world. >> > In one column for the botanical species names, some of the first >> > characters in each >> > record are not capitalized when they should all be while many are >> > properly capitalized. >> > I need to correct this. The database is "TAXA". The column is >> "species_name". >> > >> > I have been using SQL statements through both phpMyAdmin and >> > MySQL-front as front ends to the database. I get part way ok but need >> > others help to understand why the final >> > step does not work as follows: >> > >> >> .......................................................................................................................... >> > >> > This statement faithfully reports the first character in the >> > species_name field: >> > >> > SELECT UCASE(LEFT(species_name, 1)) from species; >> > >> > This statement faithfully produces all other characters after the first >> > one in the specie_name field: >> > >> > SELECT RIGHT(species_name, LENGTH(species_name)-1) FROM species; >> > >> > When I run both together, there is no error: >> > >> > SELECT UCASE(LEFT(species_name, 1)) FROM species; >> > SELECT RIGHT(species_name, LENGTH(species_name)-1) FROM species; >> > >> > When I finally try to concatenate the first character back together >> > with the rest of the species name, it does not work: >> > >> > SELECT CONCAT( >> > UCASE(LEFT(species_name, 1)) FROM species, >> > RIGHT(species_name, LENGTH(species_name)) FROM species >> > ); >> >> you have "FROM species" duplicated. A SELECT can only have one FROM >> clause (but several tables in it). Try this. >> >> SELECT CONCAT( >> UCASE(LEFT(species_name, 1)), RIGHT(species_name, >> LENGTH(species_name)) >> FROM species >> ); >> >> >> > Instead, I get an error near FROM species, RIGHT(species_name, >> > LENGTH(species_name)) FROM species >> > >> > I know I must be close but cannot find out why this is not just working. >> > >> > Also, when it finally reports the full species names correctly, does >> > it matter if I add in a SET statement to finally >> > replace the old entries or should I use an UPDATE command instead of >> > SELECT? Does it matter? >> > >> > I look forward to your suggestions. >> > >> > Bill Mudry >> > >> > >> >> -- >> Niel Archer >> >> >> >> -- >> PHP Windows Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >