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
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?
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…
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
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.
D’Hanis, Texas, and Coeur d’Alene, Idaho show as D’hanis and Coeur D’alene with this. McDonald, O’Neal shows as Mcdonald, O’neal. This is a good (i.e. simple/quick) first pass, but title case has quirks that resist a global (even national) solution.
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
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