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
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.
You can user PRE tag when you post code
Heh… and I really hate moderated forums. ;-)
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
InitCap does not matter when you write queries until your database has a case sensitive coolation
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.
Super duper function sir
:)
Perfect function. Just copy and paste into SQL Server Management, compile and no more! Tks.
Your script has saved my life!!!! Thank you very much!!!
Hi, thanks a lot for this helpful script. :)
Thanx, this is so much helpful to me!!
What is the option if you want to actually update the permanent table?
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,'')
This is fantastic and was very much useful in my case. Thanks.
Hafiz
Thank you Pinal !
Thank you Guru!
Great Function
its useful for update all kind of string , I used for customer name , address too.
Great function!
thanks !!
Thankx Pinal….. You reaaaaally saved my time .. …
Plahanov
Thanks a lot !!!
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.
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!