SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Proper Case Command


SQL Proper Case Command

Author
Message
Kyle-771813
Kyle-771813
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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?
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57311 Visits: 9730
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
Kyle-771813
Kyle-771813
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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?
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10506 Visits: 7195
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'
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57311 Visits: 9730
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
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71160 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search