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
Solved

Update to Proper Case

Posted on 2006-06-27
8
1,700 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

837 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