Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: Using DIFFERENCE in a query
05-08-2013, 05:11 PM #1
- Join Date
- Jan 2013
- Thanked 0 Times in 0 Posts
Using DIFFERENCE in a query
I'm running an SQL query using DIFFERENCE to compare lists of company names. We may get a situation where a company is referred to as "exampleco Ltd" and in another table as "exampleco Limited" and we need to be able to return that as a company match.
We also have a situation where the company may be referred to as "a.b.c Ltd" and then as "abc Ltd" but we haven't tussled with that one yet.
Our query looks like this at the moment:
SELECT X.* FROM
( SELECT company_name FROM table1
SELECT company_name FROM table2
) AS X,
SELECT company_name, DIFFERENCE(company_name) AS 'Difference'
ORDER BY company_name
We get an Error code 1305 Function table.DIFFERENCE does not exist. I know somnething is missing from the DIFFERENCE(company_name) part of the query and if someone could fill in the blanks or point out that we're barking up the wrong tree completely we would be very grateful.
Thanks in anticipation.
05-08-2013, 09:14 PM #2
This question isn't relevant to MySQL. DIFFERENCE used like this is a SQL Server feature.
And in SQL Server, you are supposed to give *TWO* arguments to the DIFFERENCE function. In other words, you are indeed asking for how different two different strings are.
So that query, as given, makes no sense at all.
I can't even figure out what you think it is trying to do.
It would have made more sense to do something like:
SELECT T1.company_name AS name1, T1.company_name AS name2, DIFFERENCE(T1.company_name, T2.company_name) AS theDifference FROM table1 AS T1, table2 AS T2 WHERE DIFFERENCE(T1.company_name, T2.company_name) < 4
MODERATOR: Please move this to the "Other databases" section.Be yourself. No one else is as qualified.
05-08-2013, 09:22 PM #3
You should be aware that under the covers DIFFERENCE simply uses SOUNDEX on the two arguments and then compares those two soundex values.
So you *can* get tons of false matches. For example, "bants" and "bamds" will show a DIFFERENCE of zero. As will "bannerings" and "boomerangs".
And in any case, only the first 4 or 5 consonants are used in the comparison (and double letters are converted to single ones).Be yourself. No one else is as qualified.