• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

Update to Proper Case

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
plpking1
Asked:
plpking1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check out this procedure:
http://vyaskn.tripod.com/code/propercase.txt
0
 
LowfatspreadCommented:
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
 
rafranciscoCommented:
You can also check this user-defined function:

http://www.sql-server-helper.com/functions/initcap.aspx
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherSenior DBACommented:
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
 
plpking1Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would make it an update on the table using the function like this:
update yourtable set yourfield = dbo.functionname(yourfield)
0
 
DarthModCommented:
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now