I inherited this function which uses regular expressions to look for where a letter should be capitalised. In the current incarnation, it looks for the pattern, space, comma, apostrophe (uses '', because the apostrophe will be embedded in a string, followed by a letter.
ALTER FUNCTION [dbo].[fnProperCase]
(
@String varchar(1023)
)
RETURNS varchar(1023)
AS
BEGIN
DECLARE @Pos int,
@Temp varchar(1023),
@Result varchar(1023)
SET @Temp = LOWER(LTRIM(RTRIM(@String)))
SET @Result = UPPER(SUBSTRING(@Temp,1,1))
SET @Temp = SUBSTRING(@Temp,2,1022)
SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
WHILE @Pos > 0
BEGIN
SET @Result = @Result + SUBSTRING(@Temp,1,@Pos) + UPPER(SUBSTRING(@Temp,@Pos+1,1))
SET @Temp = SUBSTRING(@Temp, @Pos + 2, 1022)
SET @Pos = PATINDEX('%[ ,''-][a-z]%', @Temp)
END
SET @Result = @Result + @Temp
RETURN @Result
END