Re: Changing a column to proper case

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Index of Archives]     [PHP Home]     [PHP Users]     [PHP Database Programming]     [PHP Install]     [Kernel Newbies]     [Yosemite Forum]     [PHP Books]

  Powered by Linux