-- User Define Function
Create Function dbo.[UDF_TITLE_CASE]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS varchar(MAX)
BEGIN
Declare @Xml XML
Declare @ProperCase Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=' '
SET @Xml = cast(('<A>'+replace(@String,@delimiter,'</A><A>')+'</A>') AS XML)
;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('A') AS FN(A) )
Select @ProperCase =Stuff((Select ' ' + UPPER(LEFT([Column],1))
+ LOWER(SUBSTRING([Column], 2 ,LEN([Column]))) from CTE
for xml path('') ),1,1,'')
RETURN (@ProperCase)
END
GO
-- For Example :
SELECT dbo.[UDF_PROPER_CASE]([StudentInfo_Name]) as [Student Name]
from [dbo].[Tbl_StudentInformation]
GO
SELECT [StudentInfo_Name]
as [Student Name]
from [dbo].[Tbl_StudentInformation]
GO
update [dbo].[Tbl_StudentInformation]
set [StudentInfo_Name]=dbo.[UDF_PROPER_CASE]([StudentInfo_Name])
