Change string to title case

[katehol]

I have a table of contact details, some of which are in lower case, some in title case and some in upper case. I want to change all of these to title case (ie lower case with a capital first letter). I can't seem to find a function for this though.
Any clues?

 

[Ach005ki]

Hi Katehol

I seemed to recall a script which created a function in order to do this - perhaps you can adapt it:

http://vyaskn.tripod.com/code/propercase.txt

(found at SQLTeam:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59289)

PS
I hope it is okay to post up links


Mark, somewhere near Blackburn Lancs!

 

[TysonLPrice]

Google has quite a few hits on that. Here's one:

http://vyaskn.tripod.com/code/propercase.txt

/*************************************************************************************************
		Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
                                          
Purpose:	To convert a given string to proper case

Written by:	Narayana Vyas Kondreddi
		[URL unfurl="true"]http://vyaskn.tripod.com[/URL]

Tested on: 	SQL Server 2000

Date modified:	December-5-2001 16:55 AM IST

Email: 		vyaskn@hotmail.com

Examples:

To convert the string 'william h gates' to proper case:
SELECT dbo.PROPERCASE('william h gates')

To convert the Notes field of titles table in pubs database to proper case:
SELECT dbo.PROPERCASE(notes) FROM pubs..titles
*************************************************************************************************/
CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
	IF @input IS NULL 
	BEGIN
		--Just return NULL if input string is NULL
		RETURN NULL
	END
	
	--Character variable declarations
	DECLARE @output varchar(8000)
	--Integer variable declarations
	DECLARE @ctr int, @len int, @found_at int
	--Constant declarations
	DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
	
	--Variable/Constant initializations
	SET @ctr = 1
	SET @len = LEN(@input)
	SET @output = ''
	SET @LOWER_CASE_a = 97
	SET @LOWER_CASE_z = 122
	SET @Delimiter = ' ,-'
	SET @UPPER_CASE_A = 65
	SET @UPPER_CASE_Z = 90
	
	WHILE @ctr <= @len
	BEGIN
		--This loop will take care of reccuring white spaces
		WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
		BEGIN
			SET @output = @output + SUBSTRING(@input,@ctr,1)
			SET @ctr = @ctr + 1
		END

		IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
		BEGIN
			--Converting the first character to upper case
			SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
		END
		ELSE
		BEGIN
			SET @output = @output + SUBSTRING(@input,@ctr,1)
		END
		
		SET @ctr = @ctr + 1

		WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
		BEGIN
			IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
			BEGIN
				SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
			END
			ELSE
			BEGIN
				SET @output = @output + SUBSTRING(@input,@ctr,1)
			END
			SET @ctr = @ctr + 1
		END
		
	END
RETURN @output
END

 

[Ach005ki]

** Update **

I should have mentioned this in my post, but the reason I linked to the SQLTeam thread is that there are very relevant comments in relation to exception data where proper case capitalisation may have undesired effects.



Mark, somewhere near Blackburn Lancs!

 

[katehol]

Thanks for the replies. In the end I actually came up with a really simple UPDATE statement.

UPDATE Contact
SET Contact.contact_forename = UPPER(LEFT(contact_forename,1))+LOWER(RIGHT(contact_forename,(LEN(contact_forename)-1)))
FROM Contact
WHERE LEN(contact_forename)>0

 

[TysonLPrice]

Watch out for:

select UPPER(LEFT('o''brien',1))+
LOWER(RIGHT('o''brien',(LEN('o''brien')-1)))

 

[Ach005ki]

lol, I presumed (rather than asked for clarification) that your "contact details" was a field containing multiple parts (such as title, forename, surname etc).

Silly mistake on my part, but an example would have helped.



Mark, somewhere near Blackburn Lancs!