Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column.

For example, if the sentence is:


'I like movies'

then I need the output:


'I Like Movies'

Query:

declare @a varchar(15) 

set @a = 'qWeRtY kEyBoArD'

select @a as [Normal text],
upper(@a) as [Uppercase text],
lower(@a) as [Lowercase text],
upper(left(@a,1)) + lower(substring(@a,2,len(@a))) as [Capitalize first letter only]

Here I did upper, lower, and capitalize first letter only in my column (here I put just a random word).

Here are my results:

Is there any possibilities to do that?

Any possibilities to get results without using user defined function?

I need the output Qwerty Keyboard

declare @a varchar(30); 

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag `. Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path` trick.

StackExchange Database Administrators Q#139382, answer score: 28