By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,603 Members | 1,013 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,603 IT Pros & Developers. It's quick & easy.

Soundex Function

LacrosseB0ss
100+
P: 113
Just wondering if anyone out in query-land has used "Soundex" or "Difference" before to find field values that "sound the same". Similar to LIKE but very different. I was shocked when I heard about it, so I've been playing around. Access doesn't seem to like it. Am I just incompetant?

Thanks
- LB

(me using Northwind to goof around with)
Expand|Select|Wrap|Line Numbers
  1. qrySoundexPlaying:
  2. SELECT Customers.*
  3. FROM Customers
  4. WHERE SOUNDEX(ContactName) = SOUNDEX("Ann");
  5.  
OR

Expand|Select|Wrap|Line Numbers
  1. qryDifferencePlaying
  2. SELECT Customers.*
  3. FROM Customers
  4. WHERE DIFFERENCE(ContactName, "Ann") >= 2;
  5.  
May 24 '07 #1
Share this Question
Share on Google+
2 Replies

Rabbit
Expert Mod 10K+
P: 12,441
I've heard of it but from what I hear it's not native to Access so you'll have to code your own function.

But I've used it in SAS which has a variant of the SoundEx function but it doesn't work all that well. i.e. Juan sounds like Jaime.

However, it has a different function which quantifies the difference between two strings and you can set your own threshhold. But either way, I don't think there are native functions in Access. Also of note is that using a function like this means you can't use an index to speed up the joins in a query. Which means that large queries could take a long long time.

Case in point, I used the sounds like in SAS to join 20k records to 1.2 million records. It took 10 hours. When I used =, it took 20 seconds.
May 24 '07 #2

LacrosseB0ss
100+
P: 113
I've been looking up code and how it works and etc etc etc and Juan = Jaime isn't all that bad.

Soundex takes the word and converts it to a 4 character string representation. The first letter will stay the same and then the other 3 letters represent the word without vowels. Example, my name (Matt) would be changed to MTT and you'd get a representation of M### (don't know what it is).

Obviously this can create problems. That's where Difference comes in. Difference will return a number (1 through 4, 4 being the closest match) pertaining to how close the 2 words are. Juan = Jaime would probably be a 1 or it matches but not well. If I spelled my name wrong and typed Mattt or Mett then Matt = Mett/Mattt would return a higher Difference value.

- LB

I posted this more for people who could search for this topic later. Hope it helps someone somewhere.

Case in point, I used the sounds like in SAS to join 20k records to 1.2 million records. It took 10 hours. When I used =, it took 20 seconds
And, yeah, I'm not sure the corporation would be happy with me slowing their servers to a crawl. Thanks for the reply
May 29 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.