Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    10

    Unanswered: Different Usage of LIKE in SQL Sentences

    Hi All,

    Imagine that I have two rows of records on an MS Access database like this:

    |-----|-------MyColumn-------|
    Row 0: "Human being is mortal"
    Row 1: "Human being is immortal"

    I want to list the records which contains exactly "mortal". How can I do this?

    Note:
    "SELECT * FROM MyTable WHERE MyColumn LIKE 'mortal'" returns 0 row.
    "SELECT * FROM MyTable WHERE MyColumn LIKE '%mortal%'" returns 2 rows, including "mortal" and "immortal". But I want to list the records which contains exactly "mortal".

    Thanks...

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I would move mortal and immortal to their own field.

  3. #3
    Join Date
    May 2004
    Posts
    1
    include a space before the wildcard:

    Like "* mortal"

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    philb

    That is pretty neat and it works as well, which always helps

    It is interesting in that often what you learn or have to learn in Access is often determined by stuff you already have done. For example, in my case i have "name" and "suburb" entered in a single field as in

    William J Kennedy
    Hurstville NSW 2209

    for ease of entry. As a result I have lots of queries with several right trims running in series to get such data into individual fields and as such "* mortal" etc has never been an issue for me.

    Mike

  5. #5
    Join Date
    Jul 2003
    Posts
    10
    Thanks a lot for your replies.

  6. #6
    Join Date
    Jul 2003
    Posts
    10

    Another Problem About LIKE

    Hi all,

    I have got another problem with LIKE.

    "SELECT * FROM MyTable WHERE MyColumn LIKE '% mortal %'"

    This SQL sentence returns the rows which includes "mortal" as a word. But if "mortal" is at the end of the row, it does not return that row.

    My question is: How can I select the records which contains "mortal" as an exact word anywhere in the record? (middle, beginning or end)

    Note: "mortal" is just an example, it can be any other word of course.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The closest you can get is '% mortal%" unless you want to get significantly more involved. Depends on how pissed off you would be if you returned something with "mortal" as a prefix. If it's bearable, then stick with that. Otherwise you have a considerable amount of parsing to do.
    oh yeah... documentation... I have heard of that.

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

  8. #8
    Join Date
    Jul 2003
    Posts
    10
    Any other idea?

Posting Permissions

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