Post reply

Using Lower to ensure everything but the first letter of a name is capitalised

  • August 21, 2007 at 6:34 am

    #153764

    Hello all,

    I have the following script that sets the First letter of a word to uppercase. Is there anything I can do to make everything but the first letter lowwer case where I get for Example SMITH. I can set it to Smith?

    SELECT 'sMITH' = upper ( left ( 'sMITH' ,1 ) ) + right ('sMITH', len('sMITH')-1 )

    Thanks

    Debbie

     

  • August 21, 2007 at 6:48 am

    #728268

    Hi,

    All you need is a slight change to your code.

    select

    upper(left('smith',1)) + lower(right('smith',len('smith')-1))

    Regards,

    Ian.

  • August 21, 2007 at 6:51 am

    #728270

    select upper(left('smith',1)) + lower(substring('smith',2, 8000))

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 21, 2007 at 6:52 am

    #728271

    Ah I see. So the -1 means everything but the first character.

    Thanks for that!

    Debbie

     

  • August 21, 2007 at 6:57 am

    #728274

    Im being a bit stupid but how does ,2, 8000 work?

    Debbie

  • August 21, 2007 at 7:18 am

    #728286

    The '2' means start at position no. 2 and the 8000 means all the way to position 8000, that is, all characters from 2 to 8000.

    Please be aware that this will not work for names like McGrath, MacPherson, etc.

    HTH

    Paul

  • August 21, 2007 at 10:08 am

    #728372

    And once you code for those, invariably someone in the company will bring up that you need to deal with "O'", "von" (sometimes there is no space between that and the rest of the last name), "van der", and the like. Of course, coding for these is tricky, as code that properly handles MacFarland will likewise return MacElli. Many businesses just give up and stick with initial caps across the board.

    Fun stuff, speaking from experience.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply