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
Written a long time ago but very helpful in 2013 … many thanks for your continued generosity …
I second Winans’ comment. Thanks a ton in 2013!
Still helpful six years later, thanks Pinal!
I have around 40 millions records in a column from a table which has both upper & lower string data separated by space and semicolon … For ex: “Neoplasm Metastasis; Whole-Brain Radiotherapy”
I need data which does NOT have title case for every string data…Let me the sql query for the same…
Satish
The above example is for a string.Please help me to Convert and select a column(with multiple string values) into Title Case.I’m new to SQL. Please help me
I have words like “HR Executive”. it becomes “Hr Executive”. Is it possible to exclude some special words? please help :)
Thanks a lot.
Very nice function! I usually found my answers from StackOverflow but in this case, it’s your that won. Thanks.
Thank you. It is very helpful.
But, why do we need the below condition?
UPPER (substring(@inputstring, @index +1, 1)) != ‘S’
I omit this part and it still works without any issues
I am sorry but where?
Excelent..
Very very useful
Thanks a lot
This code is needed to distinguish between the character following an apostrophe in a name and an ‘s’ following an apostrophe for a possessive.
UPPER (substring(@inputstring, @index +1, 1)) != ‘S’
If you remove that code, “O’BRIEN’S” becomes “O’brien’s”. instead of “O’Brien’s”