Solved

Need help converting 2 columns to proper case in SQL Server 2005.

Posted on 2007-04-09
3
182 Views
Last Modified: 2010-03-19
I have 2 columns in an SQL database table, LastName, and FirstName. The information entered is all mixed case. I have i.e. JOHN SMITH,  jane doe, John Doe. Is there any way for me to run a query on  those 2 columns and correct the case to proper case? I would also like to create a backup plan, like copy this table and rename it, just in case the original table gets toasted accidentally.
0
Comment
Question by:CementTruck
  • 2
3 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18878015
Hi,

here you can find a user defined function to Propercase a string:

http://pscode.com/vb/scripts/ShowCode.asp?txtCodeId=780&lngWId=5

you can use it in this form

update myTable set LastName = fn_PROPERCASE_All(LastName), firstName = fn_PROPERCASE_All(FirstName).

To make a backup of your tables, you can do

SELECT * INTO myTable_backup
FROM myTable

this will create the table myTable_backup and copy all the data there.

Hope this helps.
0
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18878273
You could create a function like
ALTER FUNCTION ProperCase
        (@text varchar(256))
RETURNS varchar(256)
AS

BEGIN
declare @counter int,
      @newtext varchar(256)
SET @counter = 2
SET @text = ltrim(rtrim(@text))
set @newtext = UPPER(SUBSTRING(@text,1,1))

WHILE @counter <= len(@text)
        BEGIN
                  IF SUBSTRING(@text,@counter - 1,1) = ' '
                        OR SUBSTRING(@text,@counter - 2,2) = 'mc'
                        OR SUBSTRING(@text,@counter - 2,2) = ' o'
                        SET @newtext = @newtext + UPPER(SUBSTRING(@text, @counter,1))
                  ELSE
                        SET @newtext = @newtext + LOWER(SUBSTRING(@text, @counter,1))
                  SET @counter = @counter + 1
        END
finish:
RETURN @newtext
END

"Proper Case" can vary.  If it is for names is hard to get it exactly right (names like terHorst, Van de Graff, de la Garza... )  And titles have rules for words that are always lower case.  You can modify the function to include the rules you want.

Once you have a function that suits your needs you can test it like:

SELECT FirstName, LastName, dbo.ProperCase(FirstName), dbo.ProperCase(LastName)
FROM YourTable

Then backup the table with:
SELECT *
INTO YourTableBackup
FROM YourTable

Then update with
UPDATE YourTable
SET FirstName = dbo.ProperCase(FirstName),
    LastName =  dbo.ProperCase(LastName)
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18950638
Did either of these two suggestions work for you?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now