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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
102 Comments. Leave new
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 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)
Hi Pin,
What is the purpose of title case?
In which situation, we need to use that ….?
By
Wilson Gunanithi . J
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.
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
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!