CODEWITHSUNDEEP
sql
user181218
user181218

Reputation: 1695

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.

Upvotes: 36

Views: 217939

Answers (8)

KIR
KIR

Reputation: 5712

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%'

Upvotes: 9

user2662006
user2662006

Reputation: 2294

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+'%' )
        )

Upvotes: 8

Rufino
Rufino

Reputation: 1

Maybe EXISTS can help.

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

Upvotes: 0

Etay Gudai 972-522-3322-47
Etay Gudai 972-522-3322-47

Reputation: 727

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

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48597

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*"');

Upvotes: 30

walt
walt

Reputation: 101

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');

Upvotes: -5

Pranay Rana
Pranay Rana

Reputation: 176956

Select * from table where 
  columnname like'%David%' and 
  columnname like '%Moses%' and columnname like'%Robi%'

Upvotes: 53

Ovais Khatri
Ovais Khatri

Reputation: 3211

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);

Upvotes: 1

Related Questions