?
Solved

Update to Proper Case

Posted on 2006-06-27
8
Medium Priority
?
1,705 Views
Last Modified: 2008-01-09
Hi - I am using SQL Server 2000. I have a table called EMPDATA - all the data is Uppercase. My plan is to run a nightly job that updates these fields. I would like to change the FIRST_NAME column and LAST_NAME column to Proper case. I have been able to change the first letter of the name upper and the rest lower case, but the names is the database are not that straight forward. Here are some of the examples of names I am dealing with:

FIRST_NAME             WOULD LIKE
JIM                           Jim
JIM JAMES                Jim James

LAST_NAME
TORRES                    Torres
TORREZ JR.               Torrez Jr.
TORRES-ARTEGA       Torres-Artega
VAN DER MARLE         Van Der Marle
TORRES ARTEGA        Torres Artega

Thanks in advance for your help.
0
Comment
Question by:plpking1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16995209
please check out this procedure:
http://vyaskn.tripod.com/code/propercase.txt
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16995223
you may want to look at this to appreicate some of the problems with "Proper Casing"

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20930381.html?query=proper+case&topics=42

this leads to a T-SQL method...
but recommends doing it externally... (it is Client side validation issue... really...
you should get the user to confirm there acceptance of the data...)
 
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21003684.html?query=proper+case&topics=42

hth
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 16995992
You can also check this user-defined function:

http://www.sql-server-helper.com/functions/initcap.aspx
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16996099
Here's my version of a function.  I can also adjust it if you want to treat any char that is not a-z or 0-9 as requiring upper case after it.  Naturally change input variable len to match what you need.


CREATE FUNCTION dbo.ProperCase
(
    --The string to be converted to proper case
    @input VARCHAR(500)
    --, @delims VARCHAR(10) = ' ,-;'  --uncomment to allow delims to be a param
)
RETURNS VARCHAR(500)
AS
BEGIN
--The character(s) to be recognized as separating strings that need capitalized
DECLARE @delims VARCHAR(10)
SET @delims = ' ,-;'

DECLARE @inputLen SMALLINT
DECLARE @byte SMALLINT
DECLARE @delimFound BIT

SET @inputLen = LEN(@input)
SET @byte = 1
SET @delimFound = 1
WHILE @byte <= @inputLen
BEGIN
      IF CHARINDEX(SUBSTRING(@input, @byte, 1), @delims) > 0
            SET @delimFound = 1
      ELSE
      BEGIN
            IF @delimFound = 1
            BEGIN
                  SET @input = STUFF(@input, @byte, 1, UPPER(SUBSTRING(@input, @byte, 1)))
                  SET @delimFound = 0
            END --IF
            ELSE
                  SET @input = STUFF(@input, @byte, 1, LOWER(SUBSTRING(@input, @byte, 1)))
      END --ELSE
      SET @byte = @byte + 1
END --WHILE
RETURN @input
END --FUNCTION
0
 

Author Comment

by:plpking1
ID: 16997280
Thank you all for your comments and suggestions. I should have mentioned this early(sorry, really) but I am trying to do this as part of a nightly job using SQL Server Agent. I usually work in access and I am (to say the least) a bit confused in SQL Server. Can I use a function provided as part of a nightly feed? How do I call it? Thank you again.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16998543
you would make it an update on the table using the function like this:
update yourtable set yourfield = dbo.functionname(yourfield)
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 17528324
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

771 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