Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    109

    Unanswered: Search on keywords

    Hi,

    I have a table which stores keywords as a comma separated list. The records are like

    1) Ad Name | car rentals, car advertising
    2) Ad Name | car, bmw, bike


    I would want to search for 'car' and would like to have only the second record listed and not the first record. But our usual query where keyword like '%car%' wouldn't work here. Is it possible to split these keywords in the query itself and then make it work?

    I hope I was clear in explaining my need.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should not have a table which stores keywords in a comma-separated list, because this violates first normal form

    you will find that the queries will be much easier to construct if you have a table which stores one row per keyword
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    109
    No, that was a question asked to me by a fellow friend and his job was to import data from a existing db to a new db. ( couldn't argue with them )

    Anyway, I came up with a query.. Not sure whether it would work always..

    select * from ads where (ad_words like '^car,' or ad_words regexp ',car,' or ad_words regexp ',car$' or ad_word regexp 'car')

    seemed to work with the present db.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if it works, i'll take your word for it, but it will never scale, because it is forced to do a table scan

    the more rows your table has, the slower the query gets

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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