SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,209
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    search result record which has a word exactly the same as the keyWord

    Code:
    Data in myTable
    (ID) message
    (1) I am a boy 
    (2) You're a girl
    (3) He is ambitious 
    (4) They are happy 
    (5) who are you

    I have above data in myTable, and I have the following code.
    Code:
     SQL code
    select ID, message
    from myTable
    where message like '%#keyWord#%'
    If keyword is "am", it will produce the following result.

    Code:
    result
    (1) I am a boy
    (3) He is ambitious

    I like to produce the following target result.
    Code:
    target result
    (1) I am a boy
    Thanks in advance

  2. #2
    SitePoint Zealot itsyM's Avatar
    Join Date
    Jul 2002
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try:

    Code:
    SELECT ID, message
    FROM myTable
    WHERE message LIKE '% am %'
    Note the space before and after the 'am'.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    itsym, that will find the word only if it has spaces before and after

    it will not find these:

    (6) Am I really?
    (7) That's who I am

    and if you then say --
    Code:
    where message like '% am %'
       or message like 'am %'
       or message like '% am'
    then you will not find

    (9) That's who I am!

    this problem is best solved with a regular expression
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    regular expressions with MySQL:

    http://dev.mysql.com/doc/mysql/en/Regexp.html

    You may use this one:

    Code:
    select ID, message
    from myTable
    where message rlike '[[:<:]]keyword[[:>:]]'
    [[:]]
    [[:>:]]
    These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    tatsächlich

    thanks, kleineme

    good answer
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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