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)
pinaldave
dude – title case is a great function, cut, paste and call – pefect
saved me the time from writing my own
Thx man, very helpful piece of code…. keep it up
Thanks for this function! Helped get my data cleansing job done quick today and saved me heaps of time. Great work :)
Hi Pin,
What is the purpose of title case?
In which situation, we need to use that ….?
By
Wilson Gunanithi . J
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)
Fantastic!… You saved me so much time! THANK YOU!!!
This is excellent!!! Sure saved me a lot of time.
Thanks a lot!!!!!!!!!!!! You saved me a lot of time.
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
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
well done and clever!
You’ve just saved me at least 1hour of coding
c o n g r a t s ;-)
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!
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.
Good Work :)
This works great on all names except for ones like
Brian McCall
Thanks a lot!
Nice! Thanks for the post.
Very nice and handy piece of work. Save my 30 min. Thanks
But what if you have a word in all CAPS? it comes out as C A P S
Anyway to get around that?
Thanks heaps! You just helped me get out of work on time on a Friday night!
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.
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 http://www.caseconvert.com/
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.
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?
[...] 22, 2008 by pinaldave I had previously written SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case and I had really enjoyed writing it. Above script converts first letter of each word from sentence [...]
Many, many thanks for this Pinal, you’ve saved me a heap of time.
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
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…
[...] SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case [...]
Thanks A lot. This helped me great dude.. Keep up the good work :)
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
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
Thanks and keep good work.
thnx dear, to save my time :)