title: How do I create sql query for searching partial matches?
body: <p>I have a set of items in db .Each <code>item</code> has a <code>name and a description</code>.I need to implement a <code>search</code> 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 <code>or</code> description.</p>
  
  <p>for example
  I have in the db ,three items</p>
  
  <pre><code>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
  </code></pre>
  
  <p>A search using keyword 'writing' should return magic marker and XPigment Liner </p>
  
  <p>A search using keyword 'mall' should return the second item</p>
  
  <p>I tried using the <code>LIKE</code> keyword and <code>IN</code> keyword separately ,..
  For <code>IN</code> keyword to work,the query has to be</p>
  
  <pre><code>SELECT DISTINCT FROM mytable WHERE name IN ('pall mall cigarettes')
  </code></pre>
  
  <p>but </p>
  
  <pre><code>SELECT DISTINCT FROM mytable WHERE name IN ('mall')
  </code></pre>
  
  <p>will return 0 rows</p>
  
  <p>I couldn't figure out how to make a query that accommodates both the name and description columns and allows partial word match..</p>
  
  <p>Can somebody help?</p>
  
  <p><strong>update:</strong></p>
  
  <p>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</p>
  
  <pre><code>...
   id           | bigint                      | not null
   description  | text                        |  
   name         | character varying(255)      | 
  ...
  </code></pre>
  
  <p>One of the records in the table is like,</p>
  
  <pre><code>id | description | name 
  21 | 133414      | magic marker
  </code></pre>