I've read through the examples and find the variety of techniques interesting. When I had to solve the problem I was looking at fixing the data as it went into the database, so my solution is a generalized function that can be applied with an Insert or Update process. I offer it as another solution if anyone finds it of interest.
CREATE FUNCTION [dbo].[FormalCase]
(
@Input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Results varchar(255)
if len(@Input)>0
Begin
Set @Input = lower(ltrim(rtrim(@Input)))
Declare @NextSpace int, @LastSpace int
Set @LastSpace = 0
Set @NextSpace = charindex(char(32),@Input,1)
While @NextSpace>@LastSpace
Begin
Set @Input = Left(@Input, @NextSpace) + upper(substring(@Input,@NextSpace + 1, 1)) + Right(@Input, len(@Input)-(@Nextspace+1))
Set @LastSpace = @NextSpace
Set @NextSpace = charindex(char(32),@Input,@LastSpace + 1)
End
SELECT @Results = Upper(left(@Input,1)) + right(@Input, len(@Input)-1)
End
Else
Set @Results= ''
RETURN @Results
END
/* UNIT TESTING
Select dbo.formalcase('a stitch in time')
*/
GO