Efficient Function To Convert Text To Title Case| TechAid24

In this post, we want to discuss a nice function to convert text to title case (Proper Case) in SQL Server. With this function, you can easily convert your desire test to a title case. Before we get started, if you want to know about split with any delimited string, please go through the following article: How to Split comma delimited string into a Table in SQL Server

The following function will convert any string to Title Case. I have this function for a long time. I do not remember that if I wrote it myself or I modified it from original source. Run Following T-SQL statement in query analyzer:

SELECT dbo.udf_TitleCase('This function will convert this string to title case!')

The output will be displayed in the Results pan as follows:


This Function Will Convert This String To Title Case!

This Function Will Convert This String To Title Case!

T-SQL code of the function is:


CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
     RETURNS VARCHAR(4000)
AS
BEGIN
     DECLARE @Index INT
     DECLARE @Char CHAR(1)
     DECLARE @OutputString VARCHAR(255)

     SET @OutputString = LOWER(@InputString)
     SET @Index = 2
     SET @OutputString =STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
     
     WHILE @Index <= LEN(@InputString)
     BEGIN
          SET @Char = SUBSTRING(@InputString, @Index, 1)
          IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
          IF @Index + 1 <= LEN(@InputString)
          BEGIN
              IF @Char != ''''
              OR
              UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
              SET @OutputString =
              STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
         END
         SET @Index = @Index + 1
     END
    RETURN ISNULL(@OutputString,'')
END