Re: Changing a column to proper case

On Wed, Apr 7, 2010 at 7:42 AM, Bill Mudry <billmudry@rogers.com> 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 > ); > > 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 > > > You don't have to duplicate the FROM part SELECT CONCAT( UCASE(LEFT(species_name, 1)), RIGHT(species_name, LENGTH(species_name)) ) FROM species; should work. Tyrael