January 27, 2009 at 8:46 am
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?
January 27, 2009 at 9:38 am
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
January 27, 2009 at 10:31 am
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?
January 27, 2009 at 10:49 am
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'
January 27, 2009 at 11:19 am
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
January 27, 2009 at 12:08 pm
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.
[font="Courier New"]--===== 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?;')
[/font]
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply