How do I remove punctuation to group by similar field values?
Posted on 2004-11-16
I thought this would be easy and would find an existing function to do this, but I'm having a hard time finding it. Perhaps there is not an easy way to do what I need? Anyway, I need to run a Select statement to group by a field: Owner, and return the top 5 with the greatest value. This would not be tough if all the owners were data entered the same way, but many are not. For example, I need to group all of these together:
I do not want to Include Sample1 Inc.
I tried to use Soundex to do this, but because many owner names may start off the same - they get the same Soundex value, but essentially they are different. I then tried to do the same using a combination of soundex on the owner and billing address, but early analysis shows it won't work the way I need.
What I think would work +90% of the time with the data I have, is if I can group on the Owner Name with no punctuation or spaces, but I could not find a function to do this. I found: replace(Owner,',',''), but this will only replace a certain character - and I need to replace any characters that are not A-Z 0-9 with ''.
I have come across some discussions that use user defined functions, or cursors that examine each character, but I am a novice with these things and don't really know how to use them, or understand if they could accomplish what I need to do.
I would really appreciate your advice on the most straightforward way to accomplish this task.