Link to home
Start Free TrialLog in
Avatar of janmarini
janmarini

asked on

How do I remove punctuation to group by similar field values?



Hi Experts,

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:

Sample Inc
Sample, Inc
Sample, Inc.
Sample,  Inc
Sample-Inc

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.

Thanks,
Jan
ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SoftEng007
SoftEng007

Nice Hilaire!
I think I'll keep that one!  Thanks! :)
Avatar of janmarini

ASKER

Worked perfectly.  Thanks!