SQL Server – User Defined Function (UDF) – How to Convert Text String to Title Case?

Hello Folks, you might be wondering for a function which would help you to convert your text input into a Title Case (or Proper Case) in SQL Server. Since T-SQL lacks a function to convert text to title case. So, it has to be User Defined.

Therefore, the following will surely accomplish you task:

CREATE FUNCTION udf_TitleCase (@a nvarchar(max))
RETURNS nvarchar(max)
AS
  BEGIN
 
    DECLARE @b int
    SET @b = 1
     
    SELECT @a = UPPER(SUBSTRING(@a,1,1))+LOWER(SUBSTRING(@a,2,LEN(@a)-1))+' '
     
    WHILE @b < LEN(@a)
      BEGIN
        SELECT @b=CHARINDEX(' ',@a,@b)
        SELECT @a=SUBSTRING(@a,1,@b)+UPPER(SUBSTRING(@a,@b+1,1))+SUBSTRING(@a,@b+2,LEN(@a)-@b+1)    
        SELECT @b=@b+1
      END
    RETURN @a
END

Now, we will test this function by giving an input:

SELECT dbo.udf_TitleCase('SQL sErVer gEEks iS a vEry gOOd coMMunity wEB sitE') AS TitleCase;

So, you can see the result below:

   

1_SQL_Server_User_Defined_Function

Hope you like it 🙂

 

Regards

Piyush Bajaj

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

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.