How to 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 a user defined function? I need the output Qwerty Keyboard
To solve sql server uppercase you can refer the below mentioned code:
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. Hope this helps!