Converting names to Title Case

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

SQL Server 2000 Forums

SQL Server Development (2000)

Converting names to Title Case

Author

Topic

Duke Leto
Starting Member

4 Posts

Posted - 2006-08-23 : 16:50:08

I have a large database of names and addresses, some of these names are stored as All Caps, but we want them all in title case. The problem is compund names as a single character string, for example, "LEONARD MCCOY" needs to become "Leonard McCoy" and not "Leonard Mccoy", which is what the simplest Algorithm would turn it into.

My plan was to get a list of common names with this issue, and then do a bulk replace of them. Can't seem to find a good and comprehensive listing of last names anywhere.

Anyone know where to find such a list or an alternative method?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 02:28:11

Go read this topic [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718[/url].

Peter Larsson
Helsingborg, Sweden

vishalj
Starting Member

32 Posts

Posted - 2006-08-24 : 10:06:58

there is a function buiolt in which is called uppercase and there is another one called lowercase
for this you can create a function and call it title case or proper case

create function properCase(@string varchar(8000)) returns varchar(8000)
as
begin

set @string = lower(@string)
declare @i int
set @i = ascii('a')
while @i <= ascii('z')
begin

set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))
set @i = @i + 1
end

set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)
return @string
end

Duke Leto
Starting Member

4 Posts

Posted - 2006-08-25 : 15:36:43

Ahem.

Perhaps I did not make this clear, but I had no problems doing the simple proper/title case converion.

My concern was with names that need to have capitals mid-string.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-25 : 16:34:07

As you said before, you need a list of names to work with.

Your best bet is searching GOOGLE.

CODO ERGO SUM

Duke Leto
Starting Member

4 Posts

Posted - 2006-08-25 : 17:08:56

I have been Googling prior, Geneology sites seem to be the best bet, but they get their name incidence data from the Social Security Administration, so I decided to go to the horses' mouth on that one. We'll see how long it takes to get a response.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-25 : 22:50:28

I hate to say it, but it appears as if your issue is not going to be worth the time it takes to resolve 100% of the time. If I were you I would just write a simple function that checks the start of each word for the few main prefaces, and change it out. I wouldn't waste too much time on this, you may end up with a function that changes things you don't want changed.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-26 : 00:57:01

quote:
Originally posted by Vinnie881

I wouldn't waste too much time on this, you may end up with a function that changes things you don't want changed.

...when it eventually finishes executing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources