Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unhappy Unanswered: String comparison query. Helpless and desperate.

    I'm writing a query that has parameters (type string) which contain telephone numbers.
    I have a table in my database that stores area codes and first few digits of a telephone numbers. Some records only contain area codes, some contain area codes and first few digits on a phone#.
    I need to compare the string I'm passing in against the data in the database, and return the closest match.
    I.E. if there's a record that contains my area code + first three digits, that's what I'll return, else I will only return a record that contains area code.
    Sometimes, records contain up to 5 or 6 first digits, sometimes only 1... there's no pattern there, so I can't compare only the first few digits, but rather the whole string and return the closest match.
    Any idea on how to do this?
    Thanks to everyone who pauses for a minute and thinks of a possible solution!!!

    P.S. In case there's a very simple solution, don't judge me. I'm usually not that stupid! I swear I tried solving this on my own.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: String comparison query. Helpless and desperate.

    Originally posted by McKinnon
    I'm writing a query that has parameters (type string) which contain telephone numbers.
    I have a table in my database that stores area codes and first few digits of a telephone numbers. Some records only contain area codes, some contain area codes and first few digits on a phone#.
    I need to compare the string I'm passing in against the data in the database, and return the closest match.
    I.E. if there's a record that contains my area code + first three digits, that's what I'll return, else I will only return a record that contains area code.
    Sometimes, records contain up to 5 or 6 first digits, sometimes only 1... there's no pattern there, so I can't compare only the first few digits, but rather the whole string and return the closest match.
    Any idea on how to do this?
    Thanks to everyone who pauses for a minute and thinks of a possible solution!!!

    P.S. In case there's a very simple solution, don't judge me. I'm usually not that stupid! I swear I tried solving this on my own.
    Use LIKE in your query ... How are you querying for matches?

  3. #3
    Join Date
    Mar 2004
    Posts
    25

    Re: String comparison query. Helpless and desperate.

    Originally posted by McKinnon
    I'm writing a query that has parameters (type string) which contain telephone numbers.
    I have a table in my database that stores area codes and first few digits of a telephone numbers. Some records only contain area codes, some contain area codes and first few digits on a phone#.
    I need to compare the string I'm passing in against the data in the database, and return the closest match.
    I.E. if there's a record that contains my area code + first three digits, that's what I'll return, else I will only return a record that contains area code.
    Sometimes, records contain up to 5 or 6 first digits, sometimes only 1... there's no pattern there, so I can't compare only the first few digits, but rather the whole string and return the closest match.
    Any idea on how to do this?
    Thanks to everyone who pauses for a minute and thinks of a possible solution!!!

    P.S. In case there's a very simple solution, don't judge me. I'm usually not that stupid! I swear I tried solving this on my own.
    The parameters you are using - are they full telephone numbers? ie, do they hold more information than you have in the database table?

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: String comparison query. Helpless and desperate.

    So what you are saying is that you could be comparing 3-10 digits of your type string with what you have in your table. Is that correct? Then you would start off with the longest string and keep on reducing the string until you find a match. i.e.

    Let us say your type string is 514-876

    And you database table has 3 records like so:

    514-81
    514-8 'THIS IS WHAT YOU SHOULD FIND
    514-222

    Here is some sample code to give you an idea, please fix syntax for your situation:

    for iCounter = Len(TYPE_STRING) to 1 step -1

    rs.Open "SELECT * FROM Table1 WHERE Telephone LIKE '" & LEFT(TYPE_STRING,iCounter) & "%'"

    if not (rs.bof and rs.eof) then

    'YOU HAVE FOUND A MATCH

    else
    'Try Again

    End If

    Next iCounter
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Barnabus -> yes, they do hold more information than what I have in my database. They're full telephone numbers, and the amount of info that I have in my database varies from record to record.
    So I can't very well use 'LIKE' as I only need one record that is exact match returned.

    Scirocco -> Thank you! It seems like you've seen a database like that before
    However, I need to process up to 20k records in one run, so I'd like to limit number of calls to the database to as few as possible. I was sure there's a simpler way to do it.

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Yes I see where that could be a problem, another way would be to make a full Select (call all the records from your database), then each time filter on those records, this way there will be no calling other then the first time (which will still amount to 1 call of 20k records as opposed to 20k calls on one record).

    Here is some sample code using 2 loops to give you an idea using filter, please fix syntax for your situation:

    dim blnFound as Boolean

    rs.Open "SELECT * FROM Table1"

    do while not (rs.Eof and blnFound)

    for iCounter = Len(TYPE_STRING) to 1 step -1

    rs.Filter="Telephone LIKE '" & LEFT(TYPE_STRING,iCounter) & "%'"

    if not (rs.bof and rs.eof) then

    'YOU HAVE FOUND A MATCH
    blnFound= True
    Exit for

    else
    'Try Again

    End If

    Next iCounter

    rs.MoveNext

    Loop

    Originally posted by McKinnon
    Barnabus -> yes, they do hold more information than what I have in my database. They're full telephone numbers, and the amount of info that I have in my database varies from record to record.
    So I can't very well use 'LIKE' as I only need one record that is exact match returned.

    Scirocco -> Thank you! It seems like you've seen a database like that before
    However, I need to process up to 20k records in one run, so I'd like to limit number of calls to the database to as few as possible. I was sure there's a simpler way to do it.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  7. #7
    Join Date
    Apr 2004
    Posts
    3

    Re:

    That works wonderfully and it's very cheap!!
    Thanks a lot Scirocco, you saved my day.

Posting Permissions

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