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.

1_sql server function to convert text string to proper case

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)

2_sql server function to convert text string to proper case

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

3_sql server function to convert text string to proper case

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *