Recently had to tackle this and came up with the following after nothing quite hit everything I wanted. This will do an entire sentence, cases for special word handling. We also had issues with single character 'words' that a lot of the simpler methods handle but not the more complicated methods. Single return variable, no loops or cursors either.
CREATE FUNCTION ProperCase(@Text AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS BEGIN
DECLARE @return NVARCHAR(MAX)
SELECT @return = COALESCE(@return + ' ', '') + Word FROM (
SELECT CASE
WHEN LOWER(value) = 'llc' THEN UPPER(value)
WHEN LOWER(value) = 'lp' THEN UPPER(value) --Add as many new special cases as needed
ELSE
CASE WHEN LEN(value) = 1
THEN UPPER(value)
ELSE UPPER(LEFT(value, 1)) + (LOWER(RIGHT(value, LEN(value) - 1)))
END
END AS Word
FROM STRING_SPLIT(@Text, ' ')
) tmp
RETURN @return
END