Results 1 to 11 of 11

Thread: Like Operator

  1. #1
    Join Date
    Sep 2006
    Posts
    11

    Unanswered: Like Operator

    Hi, I want to use the like operator with multiple inputs but get an error

    This is wat i am doing

    Select x,y,z from tableA
    Group by x,y,z
    Having x like(Select * from tableB)


    table B has just one coloumn with string patterns.

    If i do not use a sub query and just use a sub pattern like[] it works, but i need to compare multiople inputs

    Any clue any one

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "LIKE" is used strictly for comparing scalar values. If you want to find all of the records in TableA where "x" exists in TableB, you may be better served with a join:

    SELECT t1.x, t1.y, t1.z
    FROM tableA t1 INNER JOIN tableB t2 ON t1.x = t2.x
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2006
    Posts
    11
    But "x" that i am using here is a "text"... "company name"..... and table B has a pattern matching the company name eg "*IBM*" or "IBM*" etc...

    will the join work in that case?

  4. #4
    Join Date
    Sep 2006
    Posts
    11
    one pattern can have multiple matches also hence cannot use the Join

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oh...

    Hmmm..

    I'm not sure I understand exactly what you're doing, is this close?

    SELECT t1.x, t1.y, t1.z
    FROM tableA t1
    WHERE t1.x IN (SELECT * FROM tableB t2 WHERE t2.x LIKE "*" + t1.x + "*")
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2006
    Posts
    11
    close! to what you say! let me explain

    Table A has "Company name" x "company code" y "status" z.

    now in Table A i can have IBM as company name also IBM International, IBM blah blah.....

    so in Table B i create a string Pattern "IBM*" say and want all possible matches from table A for this pattern.


    It runs OK if i have just one pattern gives the result...but if i have multiple patterns how do it...

    Note if i have only one pattern i need not use table B i simply input it as a parameter "Like[]"

    so thats my problem

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ohhh... Are all the patterns in tableB "SomethingStatic*"? You could do something like:

    SELECT t1.x, t1.y, t1.z
    FROM tableA t1 INNER JOIN tableB t2 ON t1.x LIKE t2.x + "*"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2006
    Posts
    11
    hey gr8 this works...i did not know this could work using Like instead of = with a join..cool thanx...

    String pattern matching is such a pain now i gotta think of all possible patterns..

    Thanks Teddy

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is this a one-shot deal?

    I created a little record-matching utility for use in importing data form a third party administrator once. I had it take the first three letters of the incoming feed and spit out a list of the best possible matches from our production data.

    I did it with two list boxes, the first being the stuff to match, the second being our prod stuff. If we had terms that didn't match, I could see the best possiblities side by side. Then I would select the right one, press a button to update our metadata and we were good to go. Next time that import came around the database already know which vendor names matched up to our internal stuff.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2006
    Posts
    11
    yes this was a one off..interesting though the project you said..

    There is another Software that does similar Text calculations to wat u did but uses more powerfull algorithms is Athanor.
    Do tyr it if you can?

    I would love to have a look at what u did in Access though..let me know! if you can

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Mine was fairly primitive. All I did was take the first three letters of the incoming stuff and found strings that contained that term anywhere.

    I do have a slightly more comprehensive, but much slower method of comparing which I did in excel. The idea there was to break up the source word into as many three-letter terms as possible. Then I would search my target string for each of those terms. I would then store a count of the number of matching terms to effectively create a rank. Then I would evaluate the ranks for the best possible match.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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