• 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