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
