Results 1 to 2 of 2

Thread: query help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: query help

    I have a table called dialcodes which has a code and code type.

    now i want to be able to take a number and it should return the closest match to code so



    select * from dialcodes where '7735551222' like code + '%'

    should return code of 77355512 as that's the closest match

    there may also be in the table other matches such as
    773
    and
    77355
    and
    773555

    but how can I get the closest match?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    When you are using a LIKE comparison
    Eg1: 123% will return all results starting with 123.
    Eg2: %123 will return all results ending with 123
    Eg3: %123% will return all results containing 123 (beginning, middle or end)

    So What do you mean by: "the closest match" ?
    They all contain the string you are searching for.
    --------------
    Also your SQL statement appears wrong...

    Method1: Using pure SQL
    SELECT * FROM <yourtable> WHERE <field> LIKE '%' & <YourVariable> & '%'

    Method2: Using some VBA
    Dim MyVariable As String
    MyVariable = 120389
    MyVariable = "%" & MyVariable & "%"

    SELECT * FROM <yourtable> WHERE <field> LIKE MyVariable
    George
    Home | Blog

Posting Permissions

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