Hi all

I have a "countries" table in which a Country_name field has all country names in upper case.

Rather than retyping them all, which SQL statement will keep the first letter in upper case and put the rest in lower case?

Please

Many thanks in advance

John in Belgium

update countries
set Country_name
= concat(upper(left(Country_name,1))
,lower(substring(Country_name from 2 to length(Country_name)-1)))

then manually update any names that are more than one word, e.g. Costa rica

Hi Rudy

Thank you for your suggestion. Alas it crashed.

I got an error message saying

#1064 - You have an error in your SQL syntax.

I tried the various parts in a select statement and they worked until I boiled it down to

SELECT substring( Country_name
FROM 2 to 6 )
FROM countries

which seems to be the bit that causes the problem. (I used '6' just to check)

Any suggestion?

Hi again (continued)

but then I got an inspiration from your suggestion and I used

update countries
set Country_name
= concat(upper(left(Country_name,1))
,lower(right(Country_name,length(Country_name)-1)))

and this one worked perfectly well

Thanks again!

doh!!

my bad

the syntax is SUBSTRING(str FROM pos FOR len)

I am affraid that one

SELECT substr( country_name FROM 2 FOR 6 ) FROM countries

generated an error message as well ... )

let me guess, the error was... um, hold on, i've almost got it...

nope, my crystal ball isn't working

i can't seem to guess what the error might be

you didn't by any chance abbreviate the function name, did you?

Hi and thanks for your continued interest )

When I use your

the syntax is SUBSTRING(str FROM pos FOR len) in other words

select substr(country_name from 2 for 6) from countries

it crashes.

Or put otherwise, how do I select the caracters in a country_name field starting from the second one using the substr() function?

Have fun day

the function is substring, not substr

you abbreviated it!!

Aaaaarrghhhhh "All ye who did Ashton Tate dBase code lines ,for 15 years in truth I tell you ye are all doomed!"

It worked! Thanks

Have a fun day !

Best from Belgium