Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Using DIFFERENCE in a query

    Hi Guys

    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:

    [code]

    SELECT X.* FROM
    ( SELECT company_name FROM table1
    UNION
    SELECT company_name FROM table2

    ) AS X,

    SELECT company_name, DIFFERENCE(company_name) AS 'Difference'
    FROM X
    WHERE DIFFERENCE(X.company_name)>=4

    ORDER BY company_name

    [code]

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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:
    Code:
    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
    But I'm not at all sure that is what you are trying to accomplish.

    MODERATOR: Please move this to the "Other databases" section.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,441
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •