How can we 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 capitalized the first letter only in my column (here I put just a random word).
Here are my results:
Are there any possibilities to do that? Any possibilities to get results without using a 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. by using this sql server uppercase code you can Capitalize only the first letter of each word of each sentence in SQL Server