How do I create sql query for searching partial matches

Question

I have a set of items in db  Each item has a name and a description I need to implement a search facility which takes a number of keywords and returns distinct items which have at least one of the keywords matching a word in the name or description   for example I have in the db  three items  1 item1        name   magic marker     description  a writing device which makes erasable marks    on whiteboard  2 item2      name  pall mall cigarettes     description  cigarette named after a street in london  3 item3      name  XPigment Liner     description  for writing and drawing   A search using keyword  writing  should return magic marker and XPigment Liner   A search using keyword  mall  should return the second item  I tried using the LIKE keyword and IN keyword separately     For IN keyword to work the query has to be  SELECT DISTINCT FROM mytable WHERE name IN   pall mall cigarettes     but   SELECT DISTINCT FROM mytable WHERE name IN   mall     will return 0 rows  I couldn t figure out how to make a query that accommodates both the name and description columns and allows partial word match    Can somebody help   update   I created the table through hibernate and for the description field  used javax persistence  Lob annotation Using psql when I examined the table It is shown       id             bigint                        not null  description    text                             name           character varying 255                One of the records in the table is like   id   description   name  21   133414        magic marker

User · Answer

This may work as well   SELECT    FROM myTable WHERE CHARINDEX  mall   name   gt  0   OR CHARINDEX  mall   description   gt  0

User · Answer

First of all  this approach won t scale in the large  you ll need a separate index from words to item  like an inverted index    If your data is not large  you can do  SELECT DISTINCT name  FROM mytable WHERE name LIKE   mall   OR description LIKE   mall     using OR if you have multiple keywords

[sql] How do I create sql query for searching partial matches?

Examples related to sql

Examples related to keyword