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 <[email protected]> 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
>>
>