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)
102 Comments. Leave new
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…
it is very useful for me.
thanks,
This is a very useful function. I created it and incorporated the changes that others listed. It works great.
WOW! This is awesome
Thank you so much!
Hi;
Thanks for this function,
This is very good function for the converting title case.
This is a nice and useful function.
Thanks
Hi
Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
for Suggestion
Regards;
Nitin Bhagat
thanks… :)
i need function like this for my job and this is very helpful
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
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!!!!!
I think udf_ProperCase is much cleaner and simpler. Thanks Varun and PinalDave
I just keep using
Hi Varun,
what if a string starts from digits. your solution doesnot work correctly in this case.
Wow, this is awesome! Just what the doctor ordered!
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!
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.
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
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))
!= ‘ ‘
thk alot sir
hi,
Thank you very much.
you save my lots of time.
Your work is excellent.
you are genius.