Convert All CAPS Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert All CAPS Data

Author

Topic

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-15 : 16:41:05

Hello,

I have a table that I'd like to change some of the data. All of the employee information is entered in all CAPS. Is there a way to change JANE DOE, to Jane Doe?

Also, there are instances like this example "DOE, JANE"

I would like this to be "Doe, Jane" (Naturally)

Thanks,

Mike

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-04-15 : 17:27:02

check this one out.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 07:22:53

I regard this as "difficult".

"Easy" to do the basics, "hard" to do the exception.

If your definition does not have any exceptions

then you can just use whichever algortihm works best for you, doing a Google for "Proper Case" or "Title Case" will give you some starters.

What about these:

Daphne du Maurier
Joseph le Compte (there's probably a case for Jane l'Axxxx names too)
Dick van Dyke

Then there are trailing suffixes:

Jane Doe PhD
or Doe PhD, Jane

Do you have any Titles in the names list too?

Prof. Jane Doe

How do you decide where the title & first-names end and the surname finishes?

If you have both
JANE DOE
and
DOE, JANE

would you not want to standarise them both as (either)
Jane Doe
or
Doe, Jane

??

If so you have to deal with the whole first / last name issue where either/both may be multi-word.

Mary Ann Doe
Mary Doe Smith
(where "Mary Doe-Smith" is easy, of course

, but there are unhyphenated double barrel surnames too of course)

That said, you do need to capitalise hyphenated correctly - "Mary Doe-Smith" and not "Mary Doe-smith". There are other separators to consider ...

mikecoleman407
Starting Member

13 Posts

Posted - 2015-05-01 : 17:19:07

I think it's a little simpler than that in my case. So I have one table called UPEMPL and it has many rows, but I'm only concerned with 3 of them. FName, LName, and MName. The data in these rows is in all CAPS and I just want to convert it where just the first letter is capital.

Existing data looks like this:

Fname - JANE
Lname - DOE

I'd like to convert it to:

Fname - Jane
Lname - Doe

Kristen
Test

22859 Posts

Posted - 2015-05-02 : 03:29:15

quote:
Originally posted by mikecoleman407

Existing data looks like this:

Fname - JANE
Lname - DOE

In that case its a LOT simpler, i.e. all names are single-part one word.

Here's a test rig into which you can put names that might be more complex - if you don't have any then this code will work as-is.


CREATE TABLE #TEMP
(
	ID	int IDENTITY(1,1),
	Fname	varchar(20),
	Lname	varchar(20),
	IsFixed	bit
)

INSERT INTO #TEMP
(
	Fname, Lname
)
SELECT	'JANE', 'DOE' UNION ALL
SELECT	'JOHN', 'DOE' UNION ALL
SELECT	'COMPLEX NAME', 'XXX' UNION ALL
SELECT	'HYPHEN-NAME', 'XXX'
-- ... add any additional example tests here ...

-- Before
SELECT	*
FROM	#TEMP

UPDATE	U
SET	Fname = UPPER(LEFT(Fname, 1))
		+ LOWER(SUBSTRING(Fname, 2, LEN(Fname)-1)),
	Lname = UPPER(LEFT(Lname, 1))
		+ LOWER(SUBSTRING(Lname, 2, LEN(Lname)-1)),
	IsFixed = 1	-- Indicate that the row WAS processed
FROM	#TEMP AS U
WHERE	    Fname NOT LIKE '%[^A-Za-z]%'
	AND Lname NOT LIKE '%[^A-Za-z]%'

-- After
SELECT	*
FROM	#TEMP
ORDER BY CASE WHEN IsFixed = 1 THEN 2 ELSE 1 END,	-- Sort exceptions first
	ID
GO
DROP TABLE #TEMP
GO

mikecoleman407
Starting Member

13 Posts

Posted - 2015-05-05 : 16:33:15

Thanks so much for your assistance. I have one more column in the table that needs to be dealt with if you don't mind assisting. The column is called TRNSFRNAME. It has people's first and last names in it. There are a couple of people with a middle name and a couple of Jr's. Examples of the data in this column are:

JANE DOE
JANE MARIA DOE
DOE Jr. JOHN

Thanks in advance. You're assistance is always greatly appreciated.

Mike

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 03:46:30

So your data is "complex" rather than the simple data that you said ...

Look for a Title Case conversion.

e.g. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=201125

mikecoleman407
Starting Member

13 Posts

Posted - 2015-05-06 : 09:15:36

Thanks Kristen. That did the trick !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources