Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Unanswered: FoxPro: Searching two columns...

    So, Here's what I got.

    Code:
    SELECT l.streetnum, l.streetnam
    FROM tblAddresses AS l
    WHERE ((1.streetnum LIKE '%325 '%) AND (1.streetnam LIKE '%M%'))
    This will return all addresses which look like "325 M". So I get a list of addresses like "325 Market Street"

    My problem is that I can't seperate the street number from the street name in query. So what I need is something like one of these, but none of these work.

    Code:
    SELECT l.streetnum + ' ' + l.streetnam AS StreetAddress
    FROM tblAddresses AS l
    WHERE (StreetAddress LIKE '%325 M%')
    ORDER BY StreetAddress
    What is interesting about this one is that I get an error message "No column with name STREETADDRESS found" on the where clause, but if I change the StreetAddress column in the where clause to l.streetnum; but I LEAVE the StreetAddress column in the order by clause it finds the column.

    And this doesn't work.

    Code:
    SELECT l.streetnum, l.streetnam
    FROM tblAddresses AS l
    WHERE ( (l.streetnum + ' ' + l.streetnam) LIKE '%325 M%' )
    this will run, and I can search for "325" OR "Market" but if I put in "325 Market", nothing is returned. It's like it's searching for the LIKE argument in either column, not combining the columns first.

    Any help?
    Last edited by Corith; 10-12-05 at 14:16.

  2. #2
    Join Date
    Dec 2003
    Posts
    104
    I hit the wron button the first time, so if you are waiting for an email, ignore the first one.

    Are you saying you're unable to separate them or you are not supposed to? I'm unclear. From the code you're posting, it looks like you're trying to combine the two at query time into one display field.

    Anyway, when it says there is no column named 'STREETADDRESS', it's because there isn't a column by that name in your source table. It's not even there in your target until the query is actually executed. So you can't do a query or comparisons on it.

    I think you will get what you need if you were to change your query into something like:

    SELECT Alltrim(streetnum) + ' ' + streetnam AS StreetAddress;
    FROM tblAddresses;
    WHERE ((streetnum LIKE '%325%') AND (streetnam LIKE '%M%'));
    ORDER BY streetnum , streetnam

    If you are looking to separate the number from the address, just get rid of the "AS" statement:

    SELECT streetnum , streetnam ;
    FROM tblAddresses;
    WHERE ((streetnum LIKE '%325%') AND (streetnam LIKE '%M%'));
    ORDER BY streetnum , streetnam
    Last edited by DSummZZZ; 10-13-05 at 13:25.
    DSummZZZ

    Even more Fox stuff at
    www.davesummers.net/foxprolinks.htm

  3. #3
    Join Date
    Sep 2005
    Posts
    6
    Well, that won't quite give me what I need. Here is the problem. I have a field on a webpage that I CANNOT change. That field is where a customer will enter a search parameter such as "325 Market". This should return all addresses containing "325 Market". The problem is that street num and street name are stored in seperate fields. I cannot delimit the input on a "space" because they might enter "325 Market Street". Or they might just enter "Market Street". So what I need to do is first combine the street num and streetname into on field, and then run a LIKE comparison on that.

    Obviously, it sounds like I cannot do that. But that end result would be that I could search the combination of both database fields on one search parameter.

    -- Corith

  4. #4
    Join Date
    Dec 2003
    Posts
    104
    Another thing to think abou tis that they may use more than one space between '325' and 'Market'. Or that they may use 'Mkt'. Or...

    You may be able to do something like this:
    Code:
    SELECT streetnum - streetname as StreetAddress;
         FROM tblAddresses;
         WHERE ((locnum - locname LIKE '%325%Market%'));
     ORDER BY StreetAddress
    The "-" for character strings is similar to "+", the difference being to trim trailing blanks from the first character expression before concatonating the second character expression to the first.
    It would end up being the same as:
    Code:
    RTRIM(streetnum) + streetname
    but without the function call for every record.

    Another way might be to remove all spaces before doing the comparison, or even use Soundex. But I won't bother with examples unless you need them.
    DSummZZZ

    Even more Fox stuff at
    www.davesummers.net/foxprolinks.htm

Posting Permissions

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