Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Proper Case Command Expand / Collapse
Author
Message
Posted Tuesday, January 27, 2009 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 7, 2011 9:04 AM
Points: 10, Visits: 31
I have employee names in our db that were entered in all uppercase/lowercase/mixed and they need to be proper cased.

I have been looking around the web to try to find an easy and efficient way to do this, but have not found anything I would say is easy and efficient.

If anyone has any advise on this subject I would greatly appreciate it.

I have looked at the other posts on this, but having problems with the fact that I could have first name, middle name, last name. Also having names such at John O'RIELLY for example.

This is the code that currently works with two names (first, last):

DECLARE @UPPERCASENAME NVARCHAR(32)
SET @UPPERCASENAME = 'JOHN ALLEN DOE'

SELECT (upper(substring(@UPPERCASENAME, 1, 1))
+ lower(substring(@UPPERCASENAME, 2, charindex(' ', @UPPERCASENAME)-1))
+ upper(substring(@UPPERCASENAME, charindex(' ', @UPPERCASENAME) + 1, 1))
+ lower(substring(@UPPERCASENAME, charindex(' ', @UPPERCASENAME) + 2, 80)))
as LowerCaseName

I played with this a bit more and have come up with the following which works with three names (first, middle, last) but now it won't work with two names (first, last).

DECLARE @UPPERCASENAME NVARCHAR(32)
SET @UPPERCASENAME = 'JOHN ALLEN DOE'

SELECT (upper(substring(@UPPERCASENAME, 1, 1))
+ lower(substring(@UPPERCASENAME, 2, charindex(' ', @UPPERCASENAME)-1)))
+ upper(substring(@UPPERCASENAME, charindex(' ', @UPPERCASENAME) + 1, 1))
+ lower(substring(@UPPERCASENAME, charindex(' ', @UPPERCASENAME) + 2, ((charindex(' ',@UPPERCASENAME,(charindex(' ',@UPPERCASENAME))+1))- (charindex(' ',@UPPERCASENAME,1)) -1)))
+ upper(substring(@UPPERCASENAME, ((charindex(' ',@UPPERCASENAME,(charindex(' ',@UPPERCASENAME))+1))+1), 1))
+ lower(substring(@UPPERCASENAME, ((charindex(' ',@UPPERCASENAME,(charindex(' ',@UPPERCASENAME))+1))+2), 32))
as ProperCaseName

Is there some conditional way to make this work with two or three names?
Post #644219
Posted Tuesday, January 27, 2009 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
There are lots of ways to automate this. It basically boils down to, what errors do you want?

For example, you can just Title Case every word (first letter is upper, all others lower). You'll end up with "Macdonald", when it should be "MacDonald", but lots of people are used to that kind of error.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #644289
Posted Tuesday, January 27, 2009 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 7, 2011 9:04 AM
Points: 10, Visits: 31
We basically just want all the names in the field to be proper cased as you said most people are used to seeing Macdonald.

After further thought, we could just run two separate queries if we can determine how many spaces are in a field using the WHERE clause.

Does anyone have any idea how to count specific characters (spaces) in a field?
Post #644362
Posted Tuesday, January 27, 2009 10:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
kyle (1/27/2009)
We basically just want all the names in the field to be proper cased as you said most people are used to seeing Macdonald.

After further thought, we could just run two separate queries if we can determine how many spaces are in a field using the WHERE clause.

Does anyone have any idea how to count specific characters (spaces) in a field?


you can count the spaces in the field using

SELECT LEN(FIRSTNAME) - LEN(REPLACE(FIRSTNAME,' ','')) 

I did something similar recenlty where we had to sort out the casing on some names. It is difficult to do this with code alone. I ended up creeating a look-up table that had a lot of the common names we had and used this to do the conversion. We did not trust the accuracy of using UPPER statements alone.

Also i would have thought most people are used to seeing 'MacDonald' rather than 'Macdonald'

Post #644379
Posted Tuesday, January 27, 2009 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
Correct is "MacDonald", "van Dyke", "D'Angelo", but most people usually won't get upset by "Macdonald", "Van Dyke", "D'angelo". Those are wrong, but people are accustomed to the error. Just like I'm used to about half a dozen different ways to mispell my last name, or to mispronounce it even if they see it spelled correctly.

Some people, on the other hand, will get upset by those things. That's why questions about proper-case for names always involve the question of how much effort to avoid upsetting people vs how many people will be upset.

On the question of just initial-caps on every separate word, the easiest way to do it involves a Numbers/Tally table. Do you have one?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #644411
Posted Tuesday, January 27, 2009 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
OK in another shameless adaptation of Tally examples, I'll attempt to get Jeff Moden some more recognition.

Here is a propercase function based on the Tally table. here I'm checking for space, accents like O'Brian and D`Angelo, but not fiddling with Mac or Mc; (re: what happens when you propercase macaroni?) feel free to adapt it:

the problem here is if you propercase a phrase like "I like the dog's ears", it's not right, because of the single quote:
"i Like The Dog'S Ears",

I suggest changing the IN statement to only include the single space., or make a new ProperCaseAName function to use separately for firstname/lastname.

--===== Create and populate the Tally table on the fly 
SELECT TOP 11000 --equates to more than 30 years of dates        
IDENTITY(INT,1,1) AS N  
INTO dbo.Tally  
FROM MASTER.dbo.SysColumns sc1,        MASTER.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance  
ALTER TABLE dbo.Tally    
ADD CONSTRAINT PK_Tally_N         PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
ALTER FUNCTION ProperCase(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)  
BEGIN
DECLARE
@CleanedText VARCHAR(8000)
SELECT @CleanedText = ISNULL(@CleanedText,'') +  
    
--first char is always capitalized?
CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
    
WHEN SUBSTRING(@OriginalText,Tally.N -1,1) IN( ' ','''','`')  THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))
    
ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))
END
    
FROM
dbo.Tally           WHERE Tally.N <= LEN(@OriginalText)            
                
RETURN @CleanedText
END

SELECT
dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE MR D''ANGELO?;')





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #644446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse