Another option is to handle this via SQLCLR. There is even a method already available in .NET that does this: [TextInfo.ToTitleCase][1] (in `System.Globalization`). This method will Upper-Case the first letter of each word, and Lower-Case the remaining letters. Unlike the other proposals here, it also skips words that are in all upper-case, assuming them to be acronyms. Of course, if this behavior is desired, it would be easy enough to update any of the T-SQL suggestions to do this as well.

One benefit of the .NET method is that it can Upper-Case letters that are Supplementary Characters. For example: [DESERET SMALL LETTER OW][2] has an upper-case mapping of [DESERET CAPITAL LETTER OW][3] <sup>(both show up as boxes when I paste them into here)</sup>, but the `UPPER()` function does not change the lower-case version to upper-case, even when the default Collation for the current Database is set to `Latin1_General_100_CI_AS_SC`.

<!-- language: lang-sql -->

    SELECT N'DESERET SMALL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC35) AS [Thing]
    UNION ALL
    SELECT N'DESERET CAPITAL LETTER OW' AS [Label], NCHAR(0xD801)+NCHAR(0xDC0D) AS [Thing]
    UNION ALL
    SELECT N'SmallButShouldBeCapital' AS [Label], UPPER(NCHAR(0xD801)+NCHAR(0xDC35)) AS [Thing]

Returns (enlarged so you can actually see the Supplementary Character):

[![enter image description here][4]][4]

You can see the full (and current) list of characters that are lower-case and change to upper-case using the following search feature at Unicode.org (you can see the Supplementary Characters by scrolling down until you get to the "DESERET" section, or just hit <kbd>Control-F</kbd> and search for that word):

<http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AChanges_When_Titlecased%3DYes%3A%5D>

Though to be honest, this isn't a huge benefit since it is doubtful that anyone is actually using any of the Supplementary Characters that can be title-cased. Either way, here is the SQLCLR code:

<!-- language: c# -->

    using System.Data.SqlTypes;
    using System.Globalization;
    using Microsoft.SqlServer.Server;
    
    public class TitleCasing
    {
        [return: SqlFacet(MaxSize = 4000)]
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlString TitleCase([SqlFacet(MaxSize = 4000)] SqlString InputString)
        {
            TextInfo _TxtInf = new CultureInfo(InputString.LCID).TextInfo;
            return new SqlString (_TxtInf.ToTitleCase(InputString.Value));
        }
    }

Here is [@MikaelEriksson's suggestion][5] -- modified slightly to handle `NVARCHAR` data as well as skip words that are all upper-case (to more closely match the behavior of the .NET method) -- along with a test of that T-SQL implementation and of the SQLCLR implementation:

<!-- language: lang-sql -->

    SET NOCOUNT ON;
    DECLARE @a NVARCHAR(50);
    
    SET @a = N'qWeRtY kEyBoArD TEST<>&''"X one&TWO '
             + NCHAR(0xD801)+NCHAR(0xDC28)
             + N'pPLe '
             + NCHAR(0x24D0) -- ⓐ  Circled "a"
             + NCHAR(0xFF24) -- Ｄ  Full-width "D"
             + N'D';
    SELECT @a;
    
    SELECT STUFF((
           SELECT N' '
                  + IIF(UPPER(T3.V) <> T3.V COLLATE Latin1_General_100_BIN2, 
                        UPPER(LEFT(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1))
                        + LOWER(STUFF(T3.V COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')),
                        T3.V)
           FROM (SELECT CAST(REPLACE((SELECT @a AS N'*' FOR XML PATH('')), N' ', N'<X/>')
                 AS XML).query('.')) AS T1(X)
           CROSS APPLY T1.X.nodes('text()') AS T2(X)
           CROSS APPLY (SELECT T2.X.value('.', 'NVARCHAR(50)')) AS T3(V)
           FOR XML PATH(''), TYPE
           ).value('text()[1]', 'NVARCHAR(50)') COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')
                    AS [Capitalize first letter only];
    
    SELECT dbo.TitleCase(@a);

[![enter image description here][6]][6]

Another difference in behavior is that this particular T-SQL implementation splits on only spaces, whereas the `ToTitleCase()` method considers most non-letters to be word separators (hence the difference in handling of the "one&TWO" part).

Finally, one unexpected downside to the SQLCLR version is that in coming up with various tests, I found a bug in the .NET code related to its handling of the Circled Letters (which has now been [reported][7] on Microsoft Connect). The .NET library treats the Circled Letters as word separators, which is why it does not turn the "ⓐＤD" into "Ⓐｄd" as it should.


  [1]: https://msdn.microsoft.com/en-us/library/system.globalization.textinfo.totitlecase.aspx
  [2]: http://unicode.org/cldr/utility/character.jsp?a=10435
  [3]: http://unicode.org/cldr/utility/character.jsp?a=1040D
  [4]: https://i.sstatic.net/Yn70F.jpg
  [5]: http://dba.stackexchange.com/a/139389/30859
  [6]: https://i.sstatic.net/DbSJf.jpg
  [7]: https://connect.microsoft.com/VisualStudio/feedback/details/2855342/textinfo-totitlecase-incorrectly-treating-circled-letters-as-word-separators