Re: Changing a column to proper case

On Wed, Apr 7, 2010 at 7:42 AM, Bill Mudry <[email protected]> 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