CREATE FUNCTION TitleCase(@inputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @resultString VARCHAR(MAX) = ''
   DECLARE @prevChar VARCHAR(1) = ' '
   SET @inputString = LOWER(@inputString)
   DECLARE @i INT = 1
   DECLARE @len INT = LEN(@inputString)
   WHILE @i <= @len
   BEGIN
      DECLARE @currentChar VARCHAR(1) = SUBSTRING(@inputString, @i, 1)
      IF @prevChar = ' ' OR @prevChar = '''' OR @prevChar = '('
         SET @resultString += UPPER(@currentChar)
      ELSE
         SET @resultString += @currentChar
      SET @prevChar = @currentChar
      SET @i += 1
   END
   RETURN @resultString
END

SELECT dbo.TitleCase('hello world') AS TitleCaseString

Вывод: «Привет, мир»

SELECT STRING_AGG(UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))), ' ') AS TitleCaseString
FROM STRING_SPLIT('hello world', ' ')

Вывод: «Привет, мир»

SELECT CONCAT(UPPER(SUBSTRING(column_name, 1, 1)), LOWER(SUBSTRING(column_name, 2))) AS TitleCaseString
FROM table_name

Вывод: «Привет, мир!»