Solved

Update to Proper Case

Posted on 2006-06-27
8
1,699 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]
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 142

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: "HAVING CASE" Clause 1 23
Return 0 on SQL count 24 28
Unable to Uninstall Visual Studio 2015 7 25
transaction in asp.net, sql server 6 31
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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