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)

About these ads

100 thoughts on “SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

  1. Hi Pin,

    What is the purpose of title case?

    In which situation, we need to use that ….?

    By

    Wilson Gunanithi . J

  2. Hi Wilson Gunanithi . J,

    Please go through example above it. It shows what is the input and what is the output of the function. Input is regular string, output is Title Case.

    The need of this is very common. You can see the comment #3 and title of this post. Title of this post is in title case :)

    Pinal Dave (SQLAuthority.com)

  3. I was going to have my data monkeys hand-jam all this data to drop the ALL CAPS to Title Caps, but thanks to your function, and Tek-Tips forums own gmmastros for pointing it out to me, I DON’T HAVE TO, er… I Don’t Have To!

    Thanks a ton for this wonderful piece of technology.

    –Gooser

  4. Thanks heaps! I have spent some good hours trying to write my own UDF for this solution.
    It perfectly solved my problem – and saved me from much pain.
    PK

  5. Thanks mate!
    You save us 1 hour of coding but still require me 15 minutes debug incase of input has leading blanks.
    I correct this function with declare at the top of function:
    SET @Temp = LTRIM(RTRIM(@InputString))
    and the rest function, I am using this @temp instead of @InputString.

    Anyway, thank you for good work!

  6. I agree with the others, this is an awesome piece of work!

    Any idea how I can get it to play nice with isnull()? If I execute the following:

    select
    isnull((dbo.udf_TitleCase(t.column)),’Undefined’) as AliasColumn
    from
    table as t

    My all caps source data is correctly converted to title caps, but my nulls are still nulls.

    • Note the last part of the function

      RETURN ISNULL(@OutputString,'')

      If the input is null, it actually change it to empty string

      So either modify that line to

      RETURN @OutputString

      or

      change your SELECT to

      select
      isnull(nullif(dbo.udf_TitleCase(t.column),''),'Undefined') as AliasColumn
      from
      table as t

  7. I love the function. It is very useful. For my implementation, I also added char(9) to this line:

    IF @Char IN (‘ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-‘, ‘/’, ‘&’,””, ‘(‘, char(9))

    This then also will Title case when a tab separates the words.

  8. Mr. Pinal,
    I am a T-SQL Noobi. I think that I can benefit GREATLY by this function. I do not know how to apply it to my current DB. How do I implement the changes to existing tables? With 165000 fields to update, you may just likely save me DAYS! Thanks, in advance, for the help…

  9. Hello, is there a way to handle last names that begin with Mc, where the letter after the c needs to be capitalized (McDonalds)? This is a great script–handles 99% of what I need.
    Thanks

  10. Hi All

    I happen to find another solution to the same problem in a book by Joseph Sack titled ‘SQL Server2005 T-SQL Recipes’

    CREATE FUNCTION udf_ProperCase(@UnCased varchar(max))
    RETURNS varchar(max)
    AS

    BEGIN

    SET @UnCased = LOWER(@UnCased)

    DECLARE @C int

    SET @C = ASCII(‘a’)

    WHILE @C <= ASCII(‘z’)

    BEGIN

    SET @UnCased = REPLACE( @UnCased, ‘ ‘ + CHAR(@C), ‘ ‘ + CHAR(@C-32))

    SET @C = @C + 1

    END

    SET @UnCased = CHAR(ASCII(LEFT(@UnCased, 1))-32) + RIGHT(@UnCased, LEN(@UnCased)-1)

    RETURN @UnCased
    END
    GO

    Enjoy!!!!!

  11. Pinal,
    I just discovered a problem after using your function above. All of the City names that have the second word starting with the letter “S” leave that “s” as lower-case. For example, “TARPON SPRINGS” gets converted to “Tarpon springs”. After reviewing your code, I see that there is a specific line of code seemingly excluding the letter “S” from the Capitilzation. Can you explain why this was done? After removing that entire IF statement, it seems to work. Also, why was it checking for IF @Char != ” ?

    IF @Char != ””
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1))
    != ‘S’

    Thanks!

  12. The statement in the top didn’t gave the results as stated.

    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!

    But, it gave as “This Function Will Convert This string To Title Case!”. See the word “string” is not title case.

    After debugging, I REMOVED the line mentioned below and it works fine for me.

    IF @Char != ””
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1))
    != ‘S’

    Anyways, thank you very much Pinal for saving my time.

  13. Hi i have tried your function but getting the following errors
    Msg 102, Level 15, State 1, Procedure udf_TitleCase, Line 16
    Incorrect syntax near ‘’’.
    Msg 102, Level 15, State 1, Procedure udf_TitleCase, Line 19
    Incorrect syntax near ‘”’.
    Msg 102, Level 15, State 1, Procedure udf_TitleCase, Line 29
    Incorrect syntax near ‘”’.

    Could u tell me what are the necessary changes i have to make?
    My function is the same as below:

    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 (’ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-’, ‘/’, ‘&’,””, ‘(’, char(9))
    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
    GO

  14. I too got errors, I realized when you cut and past for some reason some of the quotes (‘) come over as different characters. Just delete them and type in the proper quotes.

    Also I changed the this line

    IF @Char != ””
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1))
    != ‘S’

    to the following to fix the function

    IF @Char != ”
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1))
    != ‘ ‘

  15. I have a slight problem. I am trying to convert the names of people into a combination of proper and title case. The function works for almost all of them except in cases where there are initials like “MC” (it works for “M.C.”) or a name and initials written like “JOHN TJ or TJ JOHN”. I need a modified function which can leave MC as MC and not Mc and JOHN TJ (TJ JOHN) as John TJ (TJ John) and not John Tj (Tj John). Can you help?

  16. Pingback: SQL SERVER - UDF - Function to Convert Text String to Title Case - Proper Case - Part 2 Journey to SQL Authority with Pinal Dave

  17. Pinal,
    Here’s an implementation that corrects the “word part” issue where it begins with an “s” and also catches the “Mc” part of a word to preserve the lowercase “c”. Lastly, it uses VARCHAR(max) so that we no longer have to be concerned about the size of the text being converted. Thanks for sharing your base code.

    create FUNCTION udf_TitleCase (@InputString VARCHAR(max) )
    RETURNS VARCHAR(max)
    AS
    BEGIN
    DECLARE @Index INT
    DECLARE @Char CHAR(1)
    DECLARE @OutputString VARCHAR(max)
    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 (‘m’,’M’,’ ‘, ‘;’, ‘:’, ‘!’, ‘?’, ‘,’, ‘.’, ‘_’, ‘-‘, ‘/’, ‘&’,””,'(‘,””,char(9))
    BEGIN
    IF @Index + 1 <= LEN(@InputString)
    BEGIN
    IF @Char = ”” AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != ‘S’
    BEGIN
    –MAKE THE CHARACTER AFTER AN APOST UPPER UNLESS IT IS AN S. SO O’NEIL WILL CASE CORRECTLY.
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
    END
    ELSE
    BEGIN
    IF UPPER(@Char) != ‘M’
    BEGIN
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
    END
    END

    IF UPPER(@Char) = ‘M’ AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) = ‘C’
    BEGIN
    –MAKES THE C LOWER CASE.
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,LOWER(SUBSTRING(@InputString, @Index + 1, 1)))
    SET @OutputString = STUFF(@OutputString, @Index + 2, 1,UPPER(SUBSTRING(@InputString, @Index + 2, 1)))
    –WE TOOK CARE OF THE CHAR AFTER THE C, SO WE NEED TO ADVANCE.
    SET @Index = @Index + 1
    END

    END
    END

    SET @Index = @Index + 1
    END

    RETURN ISNULL(@OutputString,”)
    END

  18. Please send me a procedure,
    using
    case ,cast
    and join conditions
    in an understanding manner
    as like be

    ex:
    create procedure
    declare @in
    set @int=…….
    orderby
    case when statments…(cast..

    inner join etc…

  19. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  20. Markus –
    Your enhancements are great but you need to set @INDEX = 1 for mcclure to become McClure. I also added a line to eliminate but where if a word starts with a space it won’t be capitalized.

    Here is your code touched up
    ————————————————————————–
    CREATE FUNCTION [dbo].[Format_TitleCase] (@InputString VARCHAR(4000) )

    RETURNS VARCHAR(max)

    AS

    BEGIN –BEGIN1

    DECLARE @Index INT
    DECLARE @Char CHAR(1)
    DECLARE @OutputString VARCHAR(max)
    SET @InputString = LTRIM(RTRIM(@InputString)) –cures problem where string starts with blank space
    SET @OutputString = LOWER(@InputString)
    SET @Index = 1
    SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
    WHILE @Index <= LEN(@InputString)
    BEGIN –BEGIN2
    SET @Char = SUBSTRING(@InputString, @Index, 1)
    IF @Char IN ('m','M',' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(',char(9))
    BEGIN –BEGIN3
    IF @Index + 1 <= LEN(@InputString)
    BEGIN –BEGIN4
    IF @Char = '''' AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
    BEGIN
    –MAKE THE CHARACTER AFTER AN APOST UPPER UNLESS IT IS AN S. SO O'NEIL WILL CASE CORRECTLY.
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
    END
    ELSE
    BEGIN
    IF UPPER(@Char) != 'M'
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
    END

    IF UPPER(@Char) = 'M' AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) = 'C'
    BEGIN
    –MAKES THE C LOWER CASE.
    SET @OutputString = STUFF(@OutputString, @Index + 1, 1,LOWER(SUBSTRING(@InputString, @Index + 1, 1)))
    SET @OutputString = STUFF(@OutputString, @Index + 2, 1,UPPER(SUBSTRING(@InputString, @Index + 2, 1)))
    –WE TOOK CARE OF THE CHAR AFTER THE C, SO WE NEED TO ADVANCE.
    SET @Index = @Index + 1
    END
    END –END4
    END –END3

    SET @Index = @Index + 1

    END –END2

    RETURN ISNULL(@OutputString,'')

    END –END1

    • Jonathan…. Great work and many thanks.

      I still have trouble with the apostrophe ‘S though.

      It doesn’t seem to be recognizing the != ‘S’ because:

      O’NEILL’S FURN GALLERY returns O’Neill’S Furn Gallery
      and
      ALEXANDRA’S returns Alexandra’S

      Any ideas would be greatly appreciated because I have many more apostrophe S’s than O’Neil’s

      Thanks
      Rod

      —————————————————————-
      CREATE FUNCTION dbo.udf_TitleCase
      (@InputString VARCHAR(4000) )
      RETURNS VARCHAR(max)
      AS
      BEGIN –BEGIN1

      DECLARE @Index INT
      DECLARE @Char CHAR(1)
      DECLARE @OutputString VARCHAR(max)
      SET @InputString = LTRIM(RTRIM(@InputString)) –cures problem where string starts with blank space
      SET @OutputString = LOWER(@InputString)
      SET @Index = 1
      SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
      WHILE @Index <= LEN(@InputString)
      BEGIN –BEGIN2
      SET @Char = SUBSTRING(@InputString, @Index, 1)
      IF @Char IN ('m','M',' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(',char(9))
      BEGIN –BEGIN3
      IF @Index + 1 <= LEN(@InputString)
      BEGIN –BEGIN4
      IF @Char = '''' AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
      BEGIN
      –MAKE THE CHARACTER AFTER AN APOST UPPER UNLESS IT IS AN S. SO O'NEIL WILL CASE CORRECTLY.
      SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
      END
      ELSE
      BEGIN
      IF UPPER(@Char) != 'M'
      SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
      END

      IF UPPER(@Char) = 'M' AND UPPER(SUBSTRING(@InputString, @Index + 1, 1)) = 'C'
      BEGIN
      –MAKES THE C LOWER CASE.
      SET @OutputString = STUFF(@OutputString, @Index + 1, 1,LOWER(SUBSTRING(@InputString, @Index + 1, 1)))
      SET @OutputString = STUFF(@OutputString, @Index + 2, 1,UPPER(SUBSTRING(@InputString, @Index + 2, 1)))
      –WE TOOK CARE OF THE CHAR AFTER THE C, SO WE NEED TO ADVANCE.
      SET @Index = @Index + 1
      END
      END –END4
      END –END3

      SET @Index = @Index + 1

      END –END2

      RETURN ISNULL(@OutputString,'')

      END –END1

      GO

  21. BTW, one great use of this function is another function – Get_Person_FullName, which automatically formats a Full Name from its parts.

    CREATE FUNCTION [dbo].[Get_Person_FullName]
    (
    @PersonID AS INT
    )
    RETURNS VARCHAR(255)
    AS
    BEGIN

    DECLARE @FullName AS VARCHAR(255)
    DECLARE @LastName AS VARCHAR(100)
    DECLARE @FirstName AS VARCHAR(100)
    DECLARE @MiddleName AS VARCHAR(100)
    DECLARE @SuffixName AS VARCHAR(100)

    SELECT @LastName = LastName, @FirstName = FirstName, @MiddleName = MiddleName, @SuffixName = S.SuffixName
    FROM dbo.Persons P LEFT OUTER JOIN
    dbo.Persons_Suffixes S ON P.SuffixID = S.SuffixID
    WHERE PersonID = @PersonID

    SET @FullName = dbo.Format_TitleCase(@LastName)

    IF (isnull(@FirstName,”) ”) AND @FirstName ‘The’
    SET @FullName = @FullName + ‘, ‘ + dbo.Format_TitleCase(@FirstName)

    IF (isnull(@MiddleName,”) ”)
    SET @FullName = @FullName + ‘ ‘ + dbo.Format_TitleCase(@MiddleName)

    IF (isnull(@SuffixName,”) ”)
    SET @FullName = @FullName + ‘ ‘ + @SuffixName

    RETURN @FullName

    END

  22. A quick way is nest a bunch of function calls to built-in string functions

    1) The obvious quickie first step is to lowercase the whole string:

    UPDATE Foobar
    SET col_b = LOWER (col_b);

    2)The first letter needs to be uppercase, so write that step:

    UPDATE Foobar
    SET col_b = UPPER (LEFT(col_b, 1) + RIGHT (col_b, 2);

    But why not put them in one update statement?

    UPDATE Foobar
    SET col_b = UPPER (LEFT(LOWER (col_b), 1) + RIGHT (LOWER (col_b), 2);

    3) You can then use the REPLACE() function to uppercase letters with a blank to their left by nesting calls inside each other. LISP programmers love this style of programming :)

    UPDATE Foobar
    SET col_b = REPLACE ..
    (REPLACE
    (REPLACE (REPLACE (col_b, ‘ a’, ‘A’),
    ‘ b’, ‘ B’),
    ..);

    Now nest the updates again

    UPDATE Foobar
    SET col_b = REPLACE ..
    (REPLACE
    (REPLACE (UPPER (LEFT(LOWER (col_b), 1) + RIGHT (LOWER (col_b), 2), ‘ a’, ‘A’),
    ‘ b’, ‘ B’),
    … );

    This does the job in one pass thru the table and the code is portable.

  23. Joe Celko’s solution can further be simplied to

    declare @Foobar table(data varchar(1000))
    insert into @Foobar
    select 'this is only for testing' union all
    select 'SEe if This workS well' union all
    select 'TESTING DATA'
    /*
    Original data
    */
    select * from @Foobar

    /*
    Modified data
    */

    select
    ltrim
    (
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    (LOWER (' '+data))
    ,' a',' A')
    ,' b',' B')
    ,' c',' C')
    ,' d',' D')
    ,' e',' E')
    ,' f',' F')
    ,' g',' G')
    ,' h',' H')
    ,' i',' I')
    ,' j',' J')
    ,' k',' K')
    ,' l',' L')
    ,' m',' M')
    ,' n',' N')
    ,' o',' O')
    ,' p',' P')
    ,' q',' Q')
    ,' r',' R')
    ,' s',' S')
    ,' t',' T')
    ,' u',' U')
    ,' v',' V')
    ,' w',' W')
    ,' x',' X')
    ,' y',' Y')
    ,' x',' Z')
    ) AS data
    from @Foobar

  24. Howdy. Nicely done. Thought I’d throw my own 2 cents in here. The following is about the fastest InitialCaps function I’ve found and it will put a cap after just about anything that isn’t a letter including special characters. Heh… it’s remarkably simple, too! The original author (George Mastros) had one heck of an idea on this one! It even beats a Tally Table solution.

    It’s a shame this forum doesn’t honor any type of formatting… the function looks quite nice when properly formatted. I’m trying to us non-breaking spaces but, if that doesn’t work, I’ll just post it flat…

    CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))
    /***************************************************************************************************
    Purpose:
    Capitalize any lower case alpha character which follows any non alpha character or single quote.

    Revision History:
    Rev 00 – 24 Feb 2010 – George Mastros – Initial concept

    http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function

    Rev 01 – 25 Sep 2010 – Jeff Moden
    – Redaction for personal use and added documentation.
    – Add no-cap single-quote by single-quote to the filter.
    ***************************************************************************************************/
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    —————————————————————————————————-
    DECLARE @Position INT
    ;
    –===== Update the first character no matter what and then find the next postionthat we
    — need to update. The collation here is essential to making this so simple.
    — A-z is equivalent to the slower A-Z
    SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))),
    @Position = PATINDEX(‘%[^A-Za-z''][a-z]%’,@String COLLATE Latin1_General_Bin)
    ;
    –===== Do the same thing over and over until we run out of places to capitalize.
    — Note the reason for the speed here is that ONLY places that need capitalization
    — are even considered for @Position using the speed of PATINDEX.
    WHILE @Position > 0
    SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))),
    @Position = PATINDEX(‘%[^A-Za-z''][a-z]%’,@String COLLATE Latin1_General_Bin)
    ;
    —————————————————————————————————-
    RETURN @String;
    END ;

  25. 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.

  26. 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

  27. 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.

  28. 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,'')

  29. 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.

  30. 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!

  31. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | Journey to SQL Authority with Pinal Dave

  32. 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

  33. 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

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

  35. Pingback: MySQL – How INSERT() Function Works for MySQL | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s