Function to Convert Text String to Proper Case in SQL Server

Many a times need arises to convert a string into proper case specially the names. Let’s have a look at a SQL Server function to convert text string to proper case.

The logic is to delimit strings on space(‘  ‘) into columns and then change the first letter of column values to upper case and then again convert the column values to space delimited string.

The below query converts the string into xml delimiting it based on space (‘  ‘).

DECLARE @Xml XML 
DECLARE @Propercase VARCHAR(max),@String varchar(MAX)
DECLARE @delimiter VARCHAR(5) 

SET @String = 'sql server' 
SET @delimiter=' ' 

-- convert string to xml. replace space with node 
SET @Xml = Cast(( '<String>' 
                  + Replace(@String, @delimiter, '</String><String>') 
                  + '</String>' ) AS XML)

SELECT @XML AS xml_Value

The output of above query is shown below.

The string ‘sql server’ is converted to xml format under the element/column ‘String’.  The next step is to convert this to a table. This is done using below query.

SELECT a.value('.', 'varchar(max)') AS strings 
         FROM   @Xml.nodes('String') AS FN(a)

To understand this method in detail refer to LINK

The next step is to convert first letter of each row into upper case and then create a space delimited list of the column values.

;WITH cte 
     AS (SELECT a.value('.', 'varchar(max)') AS strings 
         FROM   @Xml.nodes('String') AS FN(a)) 
SELECT 
Stuff((SELECT ' ' + Upper(LEFT(strings, 1)) 
         + Lower(Substring(strings, 2, Len(strings)) 
         ) 
   FROM   cte 
   FOR xml path('')), 1, 1, '') As ProperCase

The output of above method is shown below. To get more details on this refer to LINK

Let’s now wrap this logic in a function as shown below.

CREATE FUNCTION fn_getpropercase
( @String VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
--SET @String = 'ahmad osama' 

DECLARE @Xml XML 
DECLARE @Propercase VARCHAR(max) 
DECLARE @delimiter VARCHAR(5) 

SET @delimiter=' ' 

-- convert string to xml. replace space with node 
SET @Xml = Cast(( '<String>' 
                  + Replace(@String, @delimiter, '</String><String>') 
                  + '</String>' ) AS XML)
-- convert to proper case and 
-- concatenate column to string
;WITH cte 
     AS (SELECT a.value('.', 'varchar(max)') AS strings 
         FROM   @Xml.nodes('String') AS FN(a)) 
-- create space delimted list from the table 
-- refer to https://www.sqlservergeeks.com/create-comma-delimited-list-in-sql-server/
SELECT @ProperCase = Stuff((SELECT ' ' + Upper(LEFT(strings, 1)) 
                                   + Lower(Substring(strings, 2, Len(strings)) 
                                   ) 
                            FROM   cte 
                            FOR xml path('')), 1, 1, '') 


RETURN @ProperCase
END

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook