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