Today, I was cleaning data for upload in the live database. I found few discrepancies in customer names. The names did not have a standard pattern, some of them in proper case, while few others in small / upper case and also some in sentence case.
However, the client wanted these names to be converted to Proper Case. As usual, I started the easy way… the web search. But almost all solutions use loop to convert it into PROPER CASE.
I normally avoid loop to perform any operation until unless it is the last option due to performance issue.
So, I designed the solution via Xquery and converted the string to Proper Case without loop.
Let me create sample to explain it.
Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)
Go
Insert into Student Values ('Steve Masters')
Insert into Student Values ('david ortiz')
Insert into Student Values ('Michael sean ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('bjorn rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('carOL Philips')
Insert into Student Values ('Merav Netz')
GO
Create Function dbo.[UDF_PROPER_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]([Student Name]) as [Student Name]
from Student
GO
SQL Server Portal 
This is really helpful for me. i searched all the website, that’s showing looping method only but, this is awesome. Thank you so much.
This solution really helped me, thank you for the post! The string I applied it to had & in some fields, so I received an XML Parsing illegal name character error. My workaround is below.
ALTER Function [dbo].[UDF_PROPER_CASE]
(
@String VARCHAR(MAX) — Variable for string
)
RETURNS varchar(MAX)
BEGIN
Declare @Xml XML
Declare @ProperCase Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=’ ‘
IF @String LIKE ‘%&%’
BEGIN
SET @String = REPLACE (@String, ‘&’, ‘&’)
END
SET @Xml = cast((‘‘+replace(@String,@delimiter,’‘)+’‘) 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,”)
IF @ProperCase LIKE ‘%&%’
BEGIN
SET @ProperCase = REPLACE (@ProperCase, ‘&’, ‘&’)
END
RETURN (@ProperCase)
END
Very, very helpful. Thank you.