SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    An asterisk search operator inside quotations

    I was hoping this part of my query would return rows with viral infection and viral infections. This isn't possible?

    MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)


    Thanks
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Depending on what server you are querying:

    Code:
    select
    *
    from
    table_name
    where
    column_name like '%viral infection%'

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there's a problem with LIKE and a leading wildcard, however...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    there's a problem with LIKE and a leading wildcard, however...
    I'm not able to test at the moment, is this because it will be expecting a character to be present?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, the problem is that LIKE with a leading wildcard cannot use a normal index (if one exists), and requires a table scan

    whereas the fulltext index search should be close to instantaneous

    the LIKE with a leading wildcard works the same as if i asked you to go to your white pages phone book and pull out all the last names with "sto" somewhere in them -- Astor, Caston, Johnstone, Winston -- so you have to read the entire book sequentially to find them all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Ah, performance wise absolutely. I suppose I could / should have brought that up.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So with my query above, is there any way to return both viral infection as well as viral infections?

    Thank you.
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    So with my query above, is there any way to return both viral infection as well as viral infections?

    Thank you.
    Code:
    where column_name in ('viral infection', 'viral infections')

    Code:
    where (column_name = 'viral infection' or column_name = 'viral infections')

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, those work too. But the goal is to keep it in this boolean format:

    MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)

    Because sometimes I throw lots of other keywords in there like:

    "viral infection", oregano, "immune system", ill*, wart*
    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    Yes, those work too. But the goal is to keep it in this boolean format:

    MATCH (keyword) AGAINST ('"viral infection*"' in boolean mode)

    Because sometimes I throw lots of other keywords in there like:

    "viral infection", oregano, "immune system", ill*, wart*
    What does boolean format have to do with anything. If you want an absolute flexible query then use my first suggestion, though it will be slower unless you do not need the wildcard at the beginning of the string.

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    What does boolean format have to do with anything.
    It has to do with Boolean FULL TEXT searches:
    http://dev.mysql.com/doc/refman/5.5/...t-boolean.html

    Busboy, I believe your query should return both terms you are looking for the way it is now.

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guelphdad View Post
    It has to do with Boolean FULL TEXT searches:
    http://dev.mysql.com/doc/refman/5.5/...t-boolean.html

    Busboy, I believe your query should return both terms you are looking for the way it is now.
    It has nothing to do with what he is trying to accomplish.


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
  •