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`. This seems consistent with the MSDN documentation which does not list `UPPER` and `LOWER` in the chart of functions that behave differently when using an `_SC` Collation: [Collation and Unicode Support: Supplementary Characters][4].
<!-- 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):
[![Query result showing UPPER() not working with Supplementary Character][5]][5]
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][6] -- 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 u'
+ NCHAR(0x0308) -- ̈ (combining diaeresis / umlaut)
+ N'vU'
+ NCHAR(0x0308) -- ̈ (combining diaeresis / umlaut)
+ N'lA';
SELECT @a AS [Original];
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(70)')) AS T3(V)
FOR XML PATH(''), TYPE
).value('text()[1]', 'NVARCHAR(70)') COLLATE Latin1_General_100_CI_AS_SC, 1, 1, N'')
AS [Capitalize first letter only];
SELECT dbo.TitleCase(@a) AS [ToTitleCase];
[![Query result showing output of T-SQL XML code and ToTitleCase via SQLCLR][7]][7]
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).
Both implementations handle combining sequences correctly. Each of the accented letters in "üvÜlA" are comprised of a base letter and a combining diaeresis / umlaut (the two dots above each letter), and they are correctly converted to the other case in both tests.
Finally, one unexpected disadvantage 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][8] 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.
---
FYI
--
A pre-done SQLCLR function encapsulating the `TextInfo.ToTitleCase` method mentioned above is now available in the Free version of [SQL#][9] (which I wrote) as **String_ToTitleCase** and **String_ToTitleCase4k**.
😺
--
[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://msdn.microsoft.com/en-us/library/ms143726.aspx#Anchor_2
[5]: https://i.sstatic.net/Yn70F.jpg
[6]: https://dba.stackexchange.com/a/139389/30859
[7]: https://i.sstatic.net/ks9Cw.jpg
[8]: https://connect.microsoft.com/VisualStudio/feedback/details/2855342/textinfo-totitlecase-incorrectly-treating-circled-letters-as-word-separators
[9]: https://SQLsharp.com/