I posted this one a long time ago and needed to use it today, so I thought I'd post it up here as well in case anyone finds it useful.  This simply attempts to capitalize the first letter of each word for the string passed in.  Use it to help clean up some pre-existing data, but don't use it as a way of presenting your data that is stored improperly since it isn't exact. 

For example, I have some data that stores press releases, and the headlines were entered all in capitals.  In an attempt to take the formatting out of the data, I will let the web page do the capitalization and use this function to reset the headlines to normal casing.  This gives the best flexibility, since now the data can be formatted either way and the client can decide; as it is now, stored in all capitals, the client has no choice but to display it that way. 

And you guys know how I hate mixing data and formatting!

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   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
   return @Ret
end

Example:

select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')
-------------------------------------------------------------------
This,My Friends, Is A Test.What Do You Think?I Like Shaquile O'Neal

(1 row(s) affected)