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