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)

SQL Function, SQL Scripts, SQL String
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

  • Heh… like I said… the forum doesn’t honor ANY formatting… not even non-breaking spaces. The code really looks ugly compared to the original. My apologies but theres not much I can do about it.

    Reply
  • Heh… and I really hate moderated forums. ;-)

    Reply
  • Hi

    i have a question regarding this udf. English language specific the words like and, or, the are not marked as init cap, how can we handle it?

    thanks in advance:
    Abdul Rahman Khokhar

    Reply
  • Rafal Ziolkowski
    May 23, 2011 4:22 pm

    Thanks for sharing!

    I have only one remark here. I think you should declare @OutputString as VARCHAR(4000) as well. Otherwise you will loose string content over 255 chars.

    Reply
  • Super duper function sir
    :)

    Reply
  • Perfect function. Just copy and paste into SQL Server Management, compile and no more! Tks.

    Reply
  • Your script has saved my life!!!! Thank you very much!!!

    Reply
  • Hi, thanks a lot for this helpful script. :)

    Reply
  • Thanx, this is so much helpful to me!!

    Reply
  • What is the option if you want to actually update the permanent table?

    Reply
  • This what i tried on my Database but unluckily received an error that states:
    “Msg 102, Level 15, State 1, Procedure ChangeTitleCase, Line 26
    Incorrect syntax near ‘)’.”

    Please guide!!
    ———————————————————————————

    CREATE FUNCTION ChangeTitleCase (@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,'')

    Reply
  • This is fantastic and was very much useful in my case. Thanks.

    Hafiz

    Reply
  • Thank you Pinal !

    Reply
  • Thank you Guru!

    Reply
  • Rajesh Sheth
    April 2, 2012 4:10 pm

    Great Function
    its useful for update all kind of string , I used for customer name , address too.

    Reply
  • Alex Fernandes
    April 10, 2012 3:42 am

    Great function!
    thanks !!

    Reply
  • Thankx Pinal….. You reaaaaally saved my time .. …

    Plahanov

    Reply
  • Thanks a lot !!!

    Reply
  • Changed SET @Index = 2 to SET @Index = 1. Setting it as 2 assumes the first character is already uppercase — which may no always be the case.

    Reply
  • Thanks Tim for the char(9) suggestion

    Pinal, you should also change the @Output declaration to match the input size:
    DECLARE @OutputString VARCHAR(4000) –was (255)

    or long strings will return a blank!

    Reply

Leave a ReplyCancel reply

Exit mobile version