Well, if you at least have spaces between each element, you could use a string splitter (see Jeff Moden's article here:
http://www.sqlservercentral.com/articles/72993/ ) to get to each element, then as long as an element is not either of the last 2 elements, you UPPER() the leftmost character, and LOWER() the remainder. Otherwise, the last 2 elements are treated to UPPER() in their entirety. Then you reassemble the address using STUFF and FOR XML PATH('') and a space for the separator.
Hi Steve
Thanks for the post I had thought of having to do that but just wanted to know if anyone had found another way
Kind regards
Stig of the dump
Yep. Understand. This is one of those "consequences of bad database design" scenarios. I honestly can't see ANY other way that has a chance at working. At least Jeff's function is lightning fast....