469,637 Members | 1,565 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,637 developers. It's quick & easy.

Soundex Function

113 100+
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?

- 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");

Expand|Select|Wrap|Line Numbers
  1. qryDifferencePlaying
  2. SELECT Customers.*
  3. FROM Customers
  4. WHERE DIFFERENCE(ContactName, "Ann") >= 2;
May 24 '07 #1
2 4328
12,516 Expert Mod 8TB
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
113 100+
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.

Similar topics

reply views Thread by fartsniff | last post: by
6 posts views Thread by Ricky Romaya | last post: by
1 post views Thread by siliconmike | last post: by
3 posts views Thread by arthur benedetti white | last post: by
32 posts views Thread by vonclausowitz | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.