change case of data using UPDATE

[pscad]

I apologize in advance if this has already been covered. I searched for what I wanted, and did not see the answer I needed.

I have a table, products, with a column (name) that I want to update. I have some Names that are spelled in initial caps, and some all caps.

My question is: How do I change all Names to Initial Caps without losing the rest of the name? For instance, one of my products is CHECK POINT VPN Cluster. I want it to read Check Point VPN Cluster.

TIA

 

[tlbroadbent]

Check the SQL Server FAQ -

http://www.mssqlserver.com/faq/.

It is a great resource.

Neil Pike created a proper case stored procedure and the T-SQL code is listed in the FAQ at the following link.

http://www.mssqlserver.com/faq/development-proper.asp

After copying the code and creating your SP, test it with the following T-SQL code.

Declare @name varchar(255)
Set @name='CHECK POINT VPN Cluster'

Exec sp_proper @name output
Print @name

The result should be 'Check Point Vpn Cluster'. This is not quite what you want so you may need to modify the code somewhat for exceptions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[pscad]

Thanks for the input. I'd seen this on another forum (may have been the one you directed me to!), but was unsure if that was what I wanted.

One other question. I want to be able to apply this to multiple items. Do I have to run it 1 time for each product, or is there a way I can use the Like variable? i.e. like 'Check Point %' ?

Thanks again,
Pat

 

[tlbroadbent]

If you use SQL 7, you'll need to write a stored procedure with a cursor to read each value, run it through the Proper Case SP, and then update the table with the value.

In SQL 2000 you can create a UDF to convert handle the conversion. With a UDF, you can write a single update query.

Update table set column=dbo.ProperCase(column)

I just found this source for a Proper Case UDF.

http://vyaskn.tripod.com/code.htm#propercase

Another option is to attach the table to Access and create an update query using the StrConv Function with the vbProperCase option. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.