Soundex Function

[LacrosseB0ss]

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)

qrySoundexPlaying:
SELECT Customers.*
FROM Customers
WHERE SOUNDEX(ContactName) = SOUNDEX("Ann");
 

OR

qryDifferencePlaying
SELECT Customers.*
FROM Customers
WHERE DIFFERENCE(ContactName, "Ann") >= 2;
 

[Rabbit]

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.

[LacrosseB0ss]

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