[sql] sql searching multiple words in a string

What is the most efficient and elegant SQL query looking for a string containing the words "David", "Moses" and "Robi". Assume the table is named T and the column C.

This question is related to sql

The answer is


Oracle SQL:

There is the "IN" Operator in Oracle SQL which can be used for that:

select 
    namet.customerfirstname, addrt.city, addrt.postalcode
from schemax.nametable namet
join schemax.addresstable addrt on addrt.adtid = namet.natadtid
where namet.customerfirstname in ('David', 'Moses', 'Robi'); 

if you put all the searched words in a temporaray table say @tmp and column col1, then you could try this:

Select * from T where C like (Select '%'+col1+'%' from @temp);

Here is what I uses to search for multiple words in multiple columns - SQL server

Hope my answer help someone :) Thanks

declare @searchTrm varchar(MAX)='one two three ddd 20   30 comment'; 
--select value from STRING_SPLIT(@searchTrm, ' ') where trim(value)<>''
select * from Bols 
WHERE EXISTS (SELECT value  
    FROM STRING_SPLIT(@searchTrm, ' ')  
    WHERE 
        trim(value)<>''
        and(    
        BolNumber like '%'+ value+'%'
        or UserComment like '%'+ value+'%'
        or RequesterId like '%'+ value+'%' )
        )

Maybe EXISTS can help.

and exists (select 1 from @DocumentNames where pcd.Name like DocName+'%' or CD.DocumentName like DocName+'%')

In SQL Server 2005+ with Full-Text indexing switched on, I'd do the following:

SELECT *
  FROM T
 WHERE CONTAINS(C, '"David" OR "Robi" OR "Moses"');

If you wanted your search to bring back results where the result is prefixed with David, Robi or Moses you could do:

SELECT *
  FROM T
 WHERE CONTAINS(C, '"David*" OR "Robi*" OR "Moses*"');

Oracle SQL :

select * 
from MY_TABLE
where REGEXP_LIKE (company , 'Microsodt industry | goglge auto car | oracles    database')
  • company - is the database column name.
  • results - this SQL will show you if company column rows contain one of those companies (OR phrase) please note that : no wild characters are needed, it's built in.

more info at : http://www.techonthenet.com/oracle/regexp_like.php


If you care about the sequence of the terms, you may consider using a syntax like

select * from T where C like'%David%Moses%Robi%'