Replace with wild card or RegEx

1

I am trying to use the Replace function to replace all instances of last names like Mcdonald, Mcadams, O'brien, etc. with McDonald, McAdams, O'Brien. These last names are stored in UPPER case and I have used a function to covert them to Proper case. What is the easiest way to clean up these last names in my Select statement?

TIA
Bill

2 
select replace(.....
3

gbritton:

Sorry, I don't understand your reply

4

There is no magic Replace argument that will convert the names to Proper Case. What I would do is find a name-spelling reference on the web, use it to build a lookup/conversion table, and join on it. Or you could just match on common patterns and convert those, so:

select replace(replace(replace(LOWER(name), 'mcd', 'McD'), 'macd', 'MacD'),'o''br', 'O''Br' ...

But then I suspect you'd hit false positives and miss others. Worse yet, the nested replace function calls get hard to follow and maintain.

5

Thanks. That is what I am doing currently AND is what I thought you were suggesting in your original reply. I was trying to avoid the long multiple replaces. Should have made it more clear that I had already done the multiple replaces.

6

This is the kind of thing that DQS is good at. Check out the reference datasets in this article:

dqs ref datasets