SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

Following function will convert any string to Title Case. I have this function for long time. I do not remember that if I wrote it myself or I modified 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 Results pan as follows:

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

Reference : Pinal Dave (http://www.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Query Analyzer Short Cut to display the text of Stored Procedure
Next Post
SQL SERVER – Primary Key Constraints and Unique Key Constraints

Related Posts

102 Comments. Leave new

  • Vishal Vashishta
    November 14, 2012 11:33 am

    Very Useful,,, saves time

    Reply
  • Written a long time ago but very helpful in 2013 … many thanks for your continued generosity …

    Reply
  • I second Winans’ comment. Thanks a ton in 2013!

    Reply
  • Still helpful six years later, thanks Pinal!

    Reply
  • I have around 40 millions records in a column from a table which has both upper & lower string data separated by space and semicolon … For ex: “Neoplasm Metastasis; Whole-Brain Radiotherapy”
    I need data which does NOT have title case for every string data…Let me the sql query for the same…
    Satish

    Reply
  • The above example is for a string.Please help me to Convert and select a column(with multiple string values) into Title Case.I’m new to SQL. Please help me

    Reply
  • vijay arockiaraj
    January 17, 2014 9:40 am

    I have words like “HR Executive”. it becomes “Hr Executive”. Is it possible to exclude some special words? please help :)

    Reply
  • Thanks a lot.

    Reply
  • Very nice function! I usually found my answers from StackOverflow but in this case, it’s your that won. Thanks.

    Reply
  • Thank you. It is very helpful.

    But, why do we need the below condition?

    UPPER (substring(@inputstring, @index +1, 1)) != ‘S’

    I omit this part and it still works without any issues

    Reply
  • Excelent..
    Very very useful
    Thanks a lot

    Reply
  • This code is needed to distinguish between the character following an apostrophe in a name and an ‘s’ following an apostrophe for a possessive.
    UPPER (substring(@inputstring, @index +1, 1)) != ‘S’
    If you remove that code, “O’BRIEN’S” becomes “O’brien’s”. instead of “O’Brien’s”

    Reply

Leave a Reply

Menu