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)
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.
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.
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
Jonathan…. Great work and many thanks.
I still have trouble with the apostrophe ‘S though.
It doesn’t seem to be recognizing the != ‘S’ because:
O’NEILL’S FURN GALLERY returns O’Neill’S Furn Gallery
and
ALEXANDRA’S returns Alexandra’S
Any ideas would be greatly appreciated because I have many more apostrophe S’s than O’Neil’s
Thanks
Rod
—————————————————————-
CREATE FUNCTION dbo.udf_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
GO
Refer this to know to how to use single quotes effectively in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
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 :)
Thanks a Lot!!!! But it will not work for the Aphostrophe Sign
Thanks, useful code.
A quick way is nest a bunch of function calls to built-in string functions
1) The obvious quickie first step is to lowercase the whole string:
UPDATE Foobar
SET col_b = LOWER (col_b);
2)The first letter needs to be uppercase, so write that step:
UPDATE Foobar
SET col_b = UPPER (LEFT(col_b, 1) + RIGHT (col_b, 2);
But why not put them in one update statement?
UPDATE Foobar
SET col_b = UPPER (LEFT(LOWER (col_b), 1) + RIGHT (LOWER (col_b), 2);
3) You can then use the REPLACE() function to uppercase letters with a blank to their left by nesting calls inside each other. LISP programmers love this style of programming :)
UPDATE Foobar
SET col_b = REPLACE ..
(REPLACE
(REPLACE (REPLACE (col_b, ‘ a’, ‘A’),
‘ b’, ‘ B’),
..);
Now nest the updates again
UPDATE Foobar
SET col_b = REPLACE ..
(REPLACE
(REPLACE (UPPER (LEFT(LOWER (col_b), 1) + RIGHT (LOWER (col_b), 2), ‘ a’, ‘A’),
‘ b’, ‘ B’),
… );
This does the job in one pass thru the table and the code is portable.
Joe Celko Sir,
That is excellent comment. I encourage every one to read this comment. http://blog.sqlauthority.com/2007/02/01/sql-server-udf-function-to-convert-text-string-to-title-case-proper-case/#comment-57503
Regards,
Pinal
Thank you so much!
thnx dude U gave me a very nice block of code and saved my time……….
Joe Celko’s solution can further be simplied to
declare @Foobar table(data varchar(1000))
insert into @Foobar
select 'this is only for testing' union all
select 'SEe if This workS well' union all
select 'TESTING DATA'
/*
Original data
*/
select * from @Foobar
/*
Modified data
*/
select
ltrim
(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
(LOWER (' '+data))
,' a',' A')
,' b',' B')
,' c',' C')
,' d',' D')
,' e',' E')
,' f',' F')
,' g',' G')
,' h',' H')
,' i',' I')
,' j',' J')
,' k',' K')
,' l',' L')
,' m',' M')
,' n',' N')
,' o',' O')
,' p',' P')
,' q',' Q')
,' r',' R')
,' s',' S')
,' t',' T')
,' u',' U')
,' v',' V')
,' w',' W')
,' x',' X')
,' y',' Y')
,' x',' Z')
) AS data
from @Foobar
Thanks Guru.. You made my day so beautiful and easy. Appreciate your good work. Keep it up..
Hi,
Is there any function which convert only KEYWORDS in upper case from stored procedure?
Mehul
There is not tool. However you can use some sql-beatifier. Search for the same in google/bing
Thanks buddy. Good work.
Howdy. Nicely done. Thought I’d throw my own 2 cents in here. The following is about the fastest InitialCaps function I’ve found and it will put a cap after just about anything that isn’t a letter including special characters. Heh… it’s remarkably simple, too! The original author (George Mastros) had one heck of an idea on this one! It even beats a Tally Table solution.
It’s a shame this forum doesn’t honor any type of formatting… the function looks quite nice when properly formatted. I’m trying to us non-breaking spaces but, if that doesn’t work, I’ll just post it flat…
CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))
/***************************************************************************************************
Purpose:
Capitalize any lower case alpha character which follows any non alpha character or single quote.
Revision History:
Rev 00 – 24 Feb 2010 – George Mastros – Initial concept
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function
Rev 01 – 25 Sep 2010 – Jeff Moden
– Redaction for personal use and added documentation.
– Add no-cap single-quote by single-quote to the filter.
***************************************************************************************************/
RETURNS VARCHAR(8000)
AS
BEGIN
—————————————————————————————————-
DECLARE @Position INT
;
–===== Update the first character no matter what and then find the next postionthat we
— need to update. The collation here is essential to making this so simple.
— A-z is equivalent to the slower A-Z
SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))),
@Position = PATINDEX(‘%[^A-Za-z''][a-z]%’,@String COLLATE Latin1_General_Bin)
;
–===== Do the same thing over and over until we run out of places to capitalize.
— Note the reason for the speed here is that ONLY places that need capitalization
— are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))),
@Position = PATINDEX(‘%[^A-Za-z''][a-z]%’,@String COLLATE Latin1_General_Bin)
;
—————————————————————————————————-
RETURN @String;
END ;
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!
Very Useful,,, saves time
Written a long time ago but very helpful in 2013 … many thanks for your continued generosity …