Post reply

Function for Proper Case - Most easiest way

  • November 17, 2014 at 5:48 pm

    #295111

    Comments posted to this topic are about the item Function for Proper Case - Most easiest way

  • November 26, 2014 at 8:28 am

    #1761111

    Very nice, Vignesh, quite a useful time saver. I've been doing a lot of proper casing in Access for some data cleanup over the last few days, there you can get away with an update query and replace with StrConv([field or string], 3).

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • November 28, 2014 at 9:22 am

    #1761464

    The only issue I have with this function is that if the string starts with a leading number, as in an address, the leading number is converted to some other ASCII character.

    '123 anYWhere DRIVE' was converted to '<23 Anywhere Drive' (where '<' is ASCII 17)

    I have used a similar method, but explicitly declare each replace rather than looping. When run against a list of 1 million addresses, the execution plan lists both at 50%. The only advantage is that leading numbers are untouched.

    create function fn_mng_alphacase(@inputstring varchar(8000))

    returns varchar(8000)

    as

    begin

    set @inputstring =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    ' ' + LOWER(@inputstring) --Insert leading space to ensure first character is detected by replace funtions

    ,' a',' A')

    ,' b',' B')

    ,' c',' C')

    ,' d',' D')

    ,' e',' E')

    ,' f',' F')

    ,' g',' G')

    ,' h',' H')

    ,' i',' I')

    ,' j',' J')

    ,' k',' K')

    ,' l',' L')

    ,' m',' M')

    ,' n',' N')

    ,' o',' O')

    ,' p',' P')

    ,' q',' Q')

    ,' r',' R')

    ,' s',' S')

    ,' t',' T')

    ,' u',' U')

    ,' v',' V')

    ,' w',' W')

    ,' x',' X')

    ,' y',' Y')

    ,' z',' Z')

    return Right(@inputstring,len(@inputstring)-1) --remove leading space

    end

    go

  • May 14, 2015 at 4:28 pm

    #1798036

    Nice start. Need to fix the minor non-alpha problem.

Viewing 4 posts - 1 through 3 (of 3 total)

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