How to search records from a table using keywords?

I have a table like this:

Products ( 'id', 'name', 'description', 'location' )

And a search string:

开发者_JAVA百科'car 1000 london'

Now what I want to do is:

bring all records where 'car' exists in 'name' or 'description' or 'location'
and
bring all records where '1000' exists in 'name' or 'description' or 'location'
and
bring all records where 'london' exists in 'name' or 'description' or 'location'

How can I search like this..

Thanks

Here is a Dynamic query that will do what you want.

    declare @search nvarchar(max)
    declare @dyn_sql nvarchar(max)
    declare @where nvarchar(max)

    set @search = 'car 1000 london'
    set @search = rtrim(LTRIM(@search))
    set @search = REPLACE(@search,' ',',')
    set @where = ''

    while (LEN(@search) > 0)
        begin
            declare @place_holder nvarchar(100)

            if((select CHARINDEX(',',@search)) = 0)
                begin
                    set @place_holder = @search
                end
            else
                begin
                    set @place_holder = SUBSTRING(@search, 0, CHARINDEX(',',@search))
                end

            set @place_holder = REPLACE(@place_holder,',','') 

            if((select CHARINDEX(',',@search)) = 0)
                begin
                    set @search = ''
                end

            set @search = SUBSTRING(@search, CHARINDEX(',',@search)+1, LEN(@search))

            set @where = @where+'name like ''%'+@place_holder+'%'' or '
            set @where = @where+'description like ''%'+@place_holder+'%'' or '
            set @where = @where+'location like ''%'+@place_holder+'%'' or '+CHAR(10)
        end

    set @where = SUBSTRING(@where,0,len(@where)-3)

    set @dyn_sql = 
    '
    select
        *
    from
        Products
    where
        '+@where

    exec (@dyn_sql)

In InnoDB

  SELECT * FROM products p 
  WHERE (p.name LIKE '% car %' 
     OR p.description LIKE '% car %' 
     OR p.location LIKE '% car %')
UNION 
  -- same query but with '% 1000 %'
UNION 
  -- ditto with '% london %'

In MyISAM

  SELECT 
    MATCH (p.name, p.location, p.description) AGAINST('car') as relevance,
    p.* FROM products p
   WHERE MATCH (p.name, p.location, p.description) AGAINST('london')   
   ORDER BY relevance DESC
UNION 
  -- same query but with '1000' <<-- see note below
UNION 
  -- ditto with 'car'           <<-- see note below.

Match against has a minimum length of 5 chars, see:

http://dev.mysql.com/doc/refman/5.5/en/fulltext-restrictions.html