How to convert data to sql proper case?

To convert data to sql proper case -

I realise you've already got a good solution, but I thought I'd add a simpler solution utilising an Inline-Table-Valued-Function, albeit one that relies on using the upcoming "vNext" version of SQL Server, which includes the STRING_AGG() and STRING_SPLIT() functions:

IF OBJECT_ID('dbo.fn_TitleCase') IS NOT NULL

DROP FUNCTION dbo.fn_TitleCase;

GO

CREATE FUNCTION dbo.fn_TitleCase

(

    @Input nvarchar(1000)

)

RETURNS TABLE

AS

RETURN

SELECT Item = STRING_AGG(splits.Word, ' ')

FROM (

    SELECT Word = UPPER(LEFT(value, 1)) + LOWER(RIGHT(value, LEN(value) - 1))

    FROM STRING_SPLIT(@Input, ' ')

    ) splits(Word);

GO

Testing the function:

SELECT *

FROM dbo.fn_TitleCase('this is a test');

This Is A Test

SELECT *

FROM dbo.fn_TitleCase('THIS IS A TEST');

This Is A Test

See MSDN for documentation on STRING_AGG() and STRING_SPLIT()

Bear in mind the STRING_SPLIT() function does not guarantee to return items in any particular order. This can be most annoying. There is a Microsoft Feedback item requesting a column be added to the output of STRING_SPLIT to denote the order of the output. Consider upvoting that here

If you want to live on the edge, and want to use this methodology, it can be expanded to include exceptions. I've constructed an inline-table-valued-function that does just that:

CREATE FUNCTION dbo.fn_TitleCase

(

    @Input nvarchar(1000)

    , @SepList nvarchar(1)

) 

RETURNS TABLE

AS

RETURN

WITH Exceptions AS (

    SELECT v.ItemToFind

        , v.Replacement

    FROM (VALUES /* add further exceptions to the list below */

          ('mca', 'McA')

        , ('maca','MacA')

        ) v(ItemToFind, Replacement)

) 

, Source AS (

    SELECT Word = UPPER(LEFT(value, 1 )) + LOWER(RIGHT(value, LEN(value) - 1))

        , Num = ROW_NUMBER() OVER (ORDER BY GETDATE())

    FROM STRING_SPLIT(@Input, @SepList) 

)

SELECT Item = STRING_AGG(splits.Word, @SepList)

FROM (

    SELECT TOP 214748367 Word

    FROM (

        SELECT Word = REPLACE(Source.Word, Exceptions.ItemToFind, Exceptions.Replacement)

            , Source.Num

        FROM Source

        CROSS APPLY Exceptions

        WHERE Source.Word LIKE Exceptions.ItemToFind + '%'

        UNION ALL

        SELECT Word = Source.Word

            , Source.Num

        FROM Source

        WHERE NOT EXISTS (

            SELECT 1

            FROM Exceptions

            WHERE Source.Word LIKE Exceptions.ItemToFind + '%'

            )

        ) w

    ORDER BY Num

    ) splits;

GO

Testing this shows hows it works:

SELECT *

FROM dbo.fn_TitleCase('THIS IS A TEST MCADAMS MACKENZIE MACADAMS', ' ');

This Is A Test McAdams Mackenzie MacAdams