capitalizing column values 1

[onressy]

Howdy all,
in a db there is FirstName column and LastName column. These values came from a web form [which i have applied features to automatically uppercase the first char of string FirstName and the same to LastName] Now... since i prevented any further FirstName and LastName from bineg inserted in to the table in am impropper format, my next step is to change all those in the table also have this format. Soooo... i would like to uppercase first char of value in the Members table for column FirstName and to do the same for the column LastName. How can i accomplish this update query? Thanks!

 

[jbenson001]

Not sure if there is an easier way

Update Members
   Set FirstName = UPPER(Left(FirstName,1)) + Right(FirstName, Len(FirstName) - 1),
       LastName  = UPPER(Left(LastName,1)) + Right(LastName, Len(LastName) - 1)

JIm

 

[mhoyt]

I made a slight addition to JIm's (could use this function on his message <grin>) excellent solution.

USE SANDBOX

IF(OBJECT_ID('SANDBOX..Members') IS NOT NULL) DROP TABLE Members

CREATE TABLE Members (
	FirstName VARCHAR(20)
	,LastName VARCHAR(20))

INSERT INTO Members VALUES ('ABE', 'lincoln')
INSERT INTO Members VALUES ('bart', 'sIMPSON')
INSERT INTO Members VALUES ('CharLIe', 'brOwN')

Update Members
   Set FirstName = UPPER(Left(FirstName,1)) + LOWER(Right(FirstName, Len(FirstName) - 1)),
       LastName  = UPPER(Left(LastName,1)) + LOWER(Right(LastName, Len(LastName) - 1))

SELECT * FROM Members

Notice how this now gives you initial caps regardless of the orginial data format. But all credit to Jim, I only added one piece.

 

[jbenson001]

Good point mhoyt... it helps take care of any stray Caps that shouldn't be there...

Jim

 

[jbenson001]

Just a P.S to mhoyt.. Don't expect a reply from this person. They have started over 50 threads and only replied 2 times.

 

[mhoyt]

Thanks Jim. Nice to meet you and see your solution, hope it helps the poster, but regardless happy to make your acquaintence.

Mike

 

[jbenson001]

Nice to meet you as well, this site is great and has a lot of great forums and posters. I have learned so much here..

Jim