Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Sep 2006
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL statement - changing case of a letter automatically

    I am running MySQL on Windows and I have a simple database setup with a
    list of items (contained in the same column), to keep it simple - let's say it is a list of colors.

    red
    white
    blue

    I want to be able to run a sql statement to change only the first letter in each word to uppercase so it will read:

    Red
    White
    Blue

    I think there is a command (but I am not familiar with it) that is suppose to change the entire case of the database table columns
    for example with :

    UPDATE Products SET ProductName = UPPER(ProductName)
    * A WHERE clause could also be used if only certain rows needed to be changed.

    But I only want to change the first letter as mentioned - does anyone know how to go about this ? Thanks for any help you can give.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Welcome here!

    try:
    UPDATE Products SET ProductName = CONCAT(UPPER(SUBSTRING(ProductName,1,1)), LOWER(SUBSTRING(ProductName FROM 2)))
    Last edited by raf; 09-22-2006 at 09:36 AM.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Raf is perfectly correct.
    I'd like to take it one step further, however, and encourage the use of
    MySQL Functions (ver 5.x).
    In a one-off UPDATE, its not worth it, but if one needs a conversion in a select and on a repeated basis, try the following

    CREATE FUNCTION ProperCase(strWord VARCHAR(126))
    RETURNS VARCHAR(126)
    DETERMINISTIC
    RETURN REPLACE(strWord, left(strWord,1), upper(left(strWord,1)));


    SELECT ProperCase("red");

    OR

    UPDATE Products SET ProductName = ProperCase(ProductName);

    Since PHP merely passes query text through, the function is available there as well.

    With the function in the database, its available to all apps and queries that access the database, making calculations. etc consistent across apps.

    Brian
    SQL_F1

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by raf View Post
    Welcome here!

    try:
    UPDATE Products SET ProductName = CONCAT(UPPER(SUBSTRING(ProductName,1,1)), LOWER(SUBSTRING(ProductName FROM 2)))
    this will change all of the items in a field ProductName. To simply change only some of the values you can modify it like this:

    Code:
    UPDATE Products SET ProductName = 
    CONCAT(UPPER(SUBSTRING(ProductName,1,1)), 
    LOWER(SUBSTRING(ProductName FROM 2)))
    where productname in ('red','white','blue')
    any of the other values would be left untouched.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Your function looks useful Brian but I think there is something incorrect about it.
    If I run it on this sentence:

    peter piper picked a peck of pickled peppers
    every letter 'p' gets converted to upper case. I would have thought only the first letter in the entire string would be converted?

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    it will indeed capitalise all occurences of the first letter, probably not what you want...

  • #7
    New Coder
    Join Date
    Sep 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Absolutely!
    Silly and no excuses - thanks for pointing that out rather than simply following the piper.

    Using Raf's correct code:

    CREATE FUNCTION ProperCase(strWord VARCHAR(126))
    RETURNS VARCHAR(126)
    DETERMINISTIC
    RETURN CONCAT(UPPER(SUBSTRING(strWord,1,1)), LOWER(SUBSTRING(strWord, 2)));

    Brian


  •  

    Posting Permissions

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