Proper/Title Case Correction on Name Fields
Posted on 2006-06-02
Below is a function I found on the web for converting a given field to Proper/Title case. As you'd expect it converts the first letter of each word to uppercase. The problem I have is that we have a number of customers with names like AA Legal Practise and Some Law Firm LLP and rather stupidly I forgot to test the script before running it on the live database.
It has also been run on some address fields and has converted values like 1st and 4th into 1St and 4Th.
Can anyone help me amend the function so I can maybe add a list of exceptions/rules that should rename lowecase or uppercase?
This is quite important!
CREATE Function ProperCase(@Text as varchar(8000))
declare @Reset bit;
declare @Ret varchar(8000);
declare @i int;
declare @c char(1);
select @Reset = 1, @i=1, @Ret = '';
while (@i <= len(@Text))
select @c= substring(@Text,@i,1),
@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
@i = @i +1