35

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.

Improve this question
4
  • Depends on the SQL Dialect. Which server? Commented Jun 28, 2011 at 12:44
  • Are you looking to match all three words in the same field or just return all rows that match any one of the three? I read it as matching all, but it looks like many people interpreted it as any. Commented Jun 28, 2011 at 13:09
  • What RDBMS? Do you want to know if the same row in the column contains all three, or just that the column itself contains all 3 on some number of rows? What's the structure of the data, are they single names or do we need to account for additional string data? What's the actual data type? Do we need to worry about foreign language characters as well as latin? Someone who has asked 34 questions probably should know what information to provide. Commented Jun 28, 2011 at 13:14
  • Thank you all. For some reason the exact same wording with no clarifications sufficed for a friend of mine who's a DBA to provide me my answer. Thank you for you trouble. @Wiseguy - My original wording was exact, but @hamlin11 edited it, possibly making it unclear as you said. Though I'm sure he meant well. Commented Jun 28, 2011 at 13:21

9 Answers 9

Reset to default
53 
Select * from table where 
  columnname like'%David%' and 
  columnname like '%Moses%' and columnname like'%Robi%'
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

the second query is correct and searches the string for the keyword whereas the first query just retrieve values with those specific word
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%'
If you are using a join query and need to look for multiple strings in the same column, put parentheses that encompass the common ANDs
30

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

2 Comments

The way I understand the question, you'd want ANDs instead of ORs -- which also means that multiple prefixes wouldn't make sense, but thanks for adding that info.
Free Text Searching / Indexing is absolute rubbish - using this and as the index does not rebuild straight away - e.g. Auto tracking still only builds every 30 min, so using this and all you will get is angry customers who cannot find what they just entered.
9

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%'
Improve this answer

2 Comments

How about the scenario if you don't care about the sequence?
In this case see the accepted answer above
8

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

Comments

3

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

Improve this answer

Comments

1

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);
Improve this answer

3 Comments

First, mark your code as code. Second it won't work, at least not with SQL Server 2008.
@sara: I just have tested it in sql server 2008 R2
I tried this, and i get "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >="
0

Maybe EXISTS can help.

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

Comments

-1

PostgreSQL full text search example

To implement this search efficiently, you will need the full text search feature which is present in many RDMSs. Here is how you'd do it on PostgreSQL. Create the indexed table:

CREATE TABLE fts(names TEXT, age INTEGER);
ALTER TABLE fts ADD COLUMN names_ts tsvector
  GENERATED ALWAYS AS (to_tsvector('english', names)) STORED;
EOF
CREATE INDEX names_ts_gin_idx ON fts USING GIN (names_ts);

And finally you can query with:

SELECT * FROM fts WHERE names_ts @@ to_tsquery('english', 'David & Moses & Robi');

Documented at: https://www.postgresql.org/docs/17/gin.html

Tested on PostgreSQL 16.6, Ubuntu 24.10.

Improve this answer

Comments

-5

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');
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.