Solved

Update to Proper Case

Posted on 2006-06-27
8
1,698 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 142

Expert Comment

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

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
You can also check this user-defined function:

http://www.sql-server-helper.com/functions/initcap.aspx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
PAQed with no points refunded (of 500)

DarthMod
Community Support Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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

13 Experts available now in Live!

Get 1:1 Help Now