Re: Changing a column to proper case

> 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