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

Posted on 2007-04-09
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.
Question by:CementTruck
  • 2
LVL 11

Expert Comment

ID: 18878015

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

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.
LVL 10

Accepted Solution

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

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)
                  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))
                        SET @newtext = @newtext + LOWER(SUBSTRING(@text, @counter,1))
                  SET @counter = @counter + 1
RETURN @newtext

"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:
INTO YourTableBackup
FROM YourTable

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

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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