Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    39

    Cool Unanswered: From upper cse to lower case

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    39
    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?

  4. #4
    Join Date
    Sep 2004
    Posts
    39
    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!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doh!!

    my bad

    the syntax is SUBSTRING(str FROM pos FOR len)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Posts
    39
    I am affraid that one

    SELECT substr( country_name FROM 2 FOR 6 ) FROM countries

    generated an error message as well ... )

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2004
    Posts
    39
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the function is substring, not substr

    you abbreviated it!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2004
    Posts
    39
    Quote Originally Posted by r937
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •