Fix tricky Surnames

[JackBurton07]

Hi I want to write a query that will fix names with apostrophes like O' and Mc
For example I want to :

Convert all surnames in my table beginning with “O Name” to “O’ Name“.

I also want to fix all surnames beginning with “McName” to “Mc Name” (where Name represents a surname).

Help!

 

[Remou]

Perhaps:

Update tblTable
Set NameField=Replace([Surname],"O ","O' ") 
WHERE ((Left([Surname],2)="O "))

And similar for Mc.

As an aside, are you aware that in Irish it is O <space> not O' ? Some people are upset by the apostrophe.

 

[BigRed1212]

What are you going to do with John Macking and his friend Ian MacGregor?

And I'm sure that Jack Ogden will want to know what happens to Omar al-Khayyam...

You get the point.

 

[Remou]

BigRed1212

The JackBurton07 mentions "O Name", that is, O <space>, so Ogden and Omar will not be affected, similarly, MacGregor etc will not be affected by replacing Mc with Mc<space>. However, Lughaidh Ó Clérigh may be annoyed.

 

[BigRed1212]

However, Lughaidh Ó Clérigh may be annoyed.

LOL. Yes.

Omar wasn't an O example, though. He was an al(space) vs al- vs al(nospace) example. Or el.

My point was more along the line that everybody has seen Oname, O'name, McName, Mc Name, Mc'Name, etc., etc. People spell their names like they want to spell their names. If I'm a proud McName I don't want to be converted to a lowly Mc Name. Grin.

But we drift.

 

[Remou]

BigRed1212

You are right, however, I recall meddling with surnames in just such a way in order to check capitalization.

 

[BigRed1212]

That's a whole 'nuther kettle where you get to bring in good Dutch names like:

Adrianus van der Brugge

where the van der is definitely not capitalized nor written as vander.

 

[JackBurton07]

LOL @ Adrianus Van Der Bruge.