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
 Site Related Forums
 The Yak Corral
 Reader Challenge - Proper Name

Author  Topic 

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-22 : 13:31:31
Bonus points to anyone who can change column 'Lastname' to proper case in the fewest lines.

Don't forget names like:

MacBeth
Carter-White
Boyd Jr.
Smith Sr. III

Sam

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-22 : 14:54:17
1st Try!


Declare @table table (lastName varchar(1000), Propername varchar(1000), pos int default(-1))
Insert Into @table (lastname)
Select 'macbeth' Union Select 'mcdonald' Union Select 'carter-white' Union Select 'a''strange double-name' Union Select 'smith iii' Union Select 'kelly jr.' Union Select 'simpson sr.'

Update @table Set properName = upper(left(lastName,1)) + right(lower(lastName),len(lastname)-1) From @table

Declare @pos int

while exists(Select * From @table Where pos<>0)
Begin
	Update @Table 
	Set 	
		properName = case when pos>0 then left(properName,pos) + upper(substring(properName,pos+1,1)) + right(properName,len(properName)-pos-1) else properName end,
		pos = case when patindex('%[^a-z][a-z]%',right(properName,len(properName)-pos))>0 then 
				patindex('%[^a-z][a-z]%',right(properName,len(properName)-pos)) + case when pos=-1 then 0 else pos end
			else 0 end
	From @Table
	Where pos<>0
End

Update @table Set propername = left(properName,patindex('%mc_%',propername)+1) +  upper(substring(properName,patindex('%mc_%',propername)+2,1)) + right(properName,len(propername)-patindex('%mc_%',propername)-2) From @table Where propername like '%mc_%'
Update @table Set propername = left(properName,patindex('%mac_%',propername)+2) +  upper(substring(properName,patindex('%mac_%',propername)+3,1)) + right(properName,len(propername)-patindex('%mac_%',propername)-3) From @table Where propername like '%mac_%'

Update @table Set propername = replace(properName,'iii','III') From @table Where propername like '% iii%'

Select * from @table


Corey
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2004-09-23 : 12:54:26
and will this deal with O'Murphy???....or have you only hard coded "known" double-barrelled names
and will it deal with "Macari"


i'm too busy/lazy at the moment to work through the solution myself....
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-23 : 12:57:21
it would handle O'Murphy, but not Macari....

I don't typically like the Mac filter... i guess you could make assumptions when certain characters follow the 'mac'

Corey
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 13:17:29
Corey,

Nice solution and as with any "Proper" conversion, it will require a set of special cases.

Sam
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-23 : 13:35:35
oh by the way... were you looking for a function for a single name or a script for a table full of names?

Corey
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-09-23 : 14:21:56
create table #a (s varchar(20))
insert #a select 'rivett'
insert #a select 'RIVETT'
insert #a select 'mcdonald'
insert #a select 'macdonald'
insert #a select 'o''hare'
insert #a select 'o''hARE'


declare @t table (s varchar(20))
insert @t select 'mc'
insert @t select 'mac'
insert @t select 'o'''

select stuff(stuff(lower(#a.s), len(coalesce(t.s, '')) + 1, 1, upper(substring(#a.s,len(coalesce(t.s, ''))+1,1))), 1,1,upper(left(#a.s,1)))
from #a
left join @t t
on #a.s like t.s + '%'


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 14:39:33
Another solution to decode!

Where's my decoder ring?

Corey, I'm going to write a proc that'll allow operators to correct all the names that are all CAPS. It'll be an update against the table, but with a condition:

WHERE CAST (UPPER(Lastname) AS VARBINARY) = CAST (Lastname AS VARBINARY)

I figure it'll leave the names alone that're correct and do a quick fix against the others.
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 14:40:38
Nigel, I have no idea what your query is doing, there's too much STUFF in it. ;-)

It works fine without a loop though.

Sam
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 14:44:27
Nigel - Can your solution be fixed to work with a lastname like

ABBAS ORTIZ

Sam
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-09-23 : 15:07:19
create table #a (s varchar(20))
insert #a select 'rivett'
insert #a select 'RIVETT'
insert #a select 'mcdonald'
insert #a select 'macdonald'
insert #a select 'o''hare'
insert #a select 'o''hARE'
insert #a select 'ABBAS ORTIS'

declare @t table (s varchar(20), offset int)
insert @t select 'mc', len('mc')
insert @t select 'mac', len('mc')
insert @t select 'o''', len('o''')
insert @t select '% ', 1

select stuff(stuff(lower(#a.s), coalesce(charindex(right(t.s,t.offset), #a.s) - 1,0) + coalesce(t.offset, 0) + 1, 1, upper(substring(#a.s,coalesce(charindex(right(t.s,t.offset), #a.s) - 1,0) + coalesce(t.offset, 0)+1,1))), 1,1,upper(left(#a.s,1)))
from #a
left join @t t
on #a.s like t.s + '%'


Beware of 'macabbas ortis' though - that will give a duplicate.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 15:47:43
Wow.

Um... Some last names end with Jr. Sr. III

Doe Jr.
Smith Sr. III

Sam
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-23 : 15:52:43
Sam, my example will handle that...

the only special cases are when you need to capitalize something in the middle of a string of letters...
ie. McA..., MacD..., or III

Add names to the table and test it out... should work for most names.

Corey
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-23 : 15:54:54
The problem I see with nigel's is it seems it can only do one correction per name.

But then again, I may be reading it wrong...

Corey
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2004-09-23 : 15:57:54
Corey,

Your solution works, and for all the cases posted. Nigel's solution doesn't require a loop and position tracking column. It's simpler, but it doesn't handle the III suffix problem.

Sam
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-09-23 : 16:35:57
You could always use it as a function...


/*
--Drop function dbo.properName
create function dbo.properName
(
	@name varchar(1000)
)
Returns varchar(1000) As
Begin
	Declare @pos int

	if (len(@name)>1)
	Begin
		Set @pos = -1
		Set @name = lower(@name)
	
		Set @name = upper(left(@name,1)) + right(lower(@name),len(@name)-1)
		While (@pos<>0)
		Begin
			Select @name = case when @pos>0 then left(@name,@pos) + upper(substring(@name,@pos+1,1)) + right(@name,len(@name)-@pos-1) else @name end,
				@pos = case when patindex('%[^a-z][a-z]%',right(@name,len(@name)-@pos))>0 then 
						patindex('%[^a-z][a-z]%',right(@name,len(@name)-@pos)) + case when @pos=-1 then 0 else @pos end
					else 0 end
		End
		if (patindex('%mc_%',@name)>0) Set @name = left(@name,patindex('%mc_%',@name)+1) +  upper(substring(@name,patindex('%mc_%',@name)+2,1)) + right(@name,len(@name)-patindex('%mc_%',@name)-2)
		if (patindex('%mac_%',@name)>0) Set @name = left(@name,patindex('%mac_%',@name)+2) +  upper(substring(@name,patindex('%mac_%',@name)+3,1)) + right(@name,len(@name)-patindex('%mac_%',@name)-3)
		
		if (patindex('% iii%',@name)>0) Set @name = replace(@name,'iii','III')
	End
	Return @name
End
*/
Declare @table table (lastName varchar(1000))
Insert Into @table (lastname)
Select 'macbeth' Union Select 'mcdonald' Union Select 'carter-white' Union Select 'a''strange double-name' Union Select 'smith iii' Union Select 'kelly jr.' Union Select 'simpson sr.'

Select 
	lastname,
	properName = dbo.properName(lastname) 
From @table




Corey
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2004-09-23 : 18:44:41
My solution needs a separate clause for each substitution and I don't see how you can get round that without a loop.
Try sql server 2005 express with a clr - VB.net has a proper name instruction.
Panic now while there's still time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site