Function for Proper Case - Most easiest way

Question

Function for Proper Case - Most easiest way

ece.viky

Right there with Babe

Points: 727
More actions
November 17, 2014 at 5:48 pm

#295111

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

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

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

Wayne West SSC-Insane Points: 22586 More actions · Answer 1

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]

Gamendorf Grasshopper Points: 12 More actions · Answer 2

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

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