Posts Tagged ‘SQL SERVER – Proper Case User-Defined Function’

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)

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')

Create Function dbo.[UDF_PROPER_CASE]
      @String VARCHAR(MAX)  -- Variable for string
RETURNS varchar(MAX)
     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)
-- For Example :
SELECT dbo.[UDF_PROPER_CASE]([Student Name]) as [Student Name]
from Student

Read Full Post »