sql searching multiple words in a string

Question

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

User · Answer

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

User · Answer

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

User · Answer

Select   from table where    columnname like  David   and    columnname like   Moses   and columnname like  Robi

User · Answer

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

User · Answer

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

User · Answer

Maybe EXISTS can help   and exists  select 1 from  DocumentNames where pcd Name like DocName     or CD DocumentName like DocName

User · Answer

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

User · Answer

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

[sql] sql searching multiple words in a string

Examples related to sql