SQL query - match any part of string

Question

I have a table the contains a VARCHAR column called - DOC_NAME. I would like to query this column for any string matches, partial matches are accepted as well.

For example, the column contains:

"How dangerous Virus and Spyware attach on the system – Techicode"

If I search for "virus spyware" I get no results. I don't want to search each word separately, as this might return irrelevant results. Also, I want to search for 'keywords' and not necessary the whole string.

I've tried the following options:

select * from docs where DOC_NAME like '%virus spyware%';

I've also tried:

SELECT * FROM docs WHERE DOC_NAME LIKE CONCAT('%','virus spyware','%')

But as I've mentioned, I don't any results back.

I there a query I can use that will return a value?

Thanks in advance.

@gpullen That's exactly what I'm trying to avoid, I want to search just 'keywords' not the whole string. — David Faizulaev
– David Faizulaev, Commented Nov 11, 2014 at 21:46

Tim Schmelter · Accepted Answer · 2014-11-11 21:51:47Z

13

select * from docs where DOC_NAME like '%virus%spyware%'

or if the order doesn't matter and only one needs to match:

select * from docs where DOC_NAME like '%virus%' or DOC_NAME like '%spyware%'

if all keywords must match use AND instead of OR.

edited Nov 11, 2014 at 21:51

answered Nov 11, 2014 at 21:46

Tim Schmelter

462k79 gold badges718 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

David Faizulaev Over a year ago

@Tim_Schmelter Is there a way to use a dynamic 'select' query? As I do not know how many words I would require to match.

Tim Schmelter Over a year ago

You can use dynamic sql and you can generate dynamic sql/parameters(but you haven't even mentioned where you use/generate the sql). Or you could fill a (temp-)table with the key-words and use that to join with this table.

David Faizulaev Over a year ago

Can you please provide with an example query for this issue? Using dynamic SQL? As I would require this wildcard and dynamic search for multiple tables

Tim Schmelter Over a year ago

@DavidFaiz: how and from where do you pass the parameters at all? You know how to insert records into a temp-table? Then you could use Join or (better) Exists: select * from docs d where exists(select 1 from keywords kw where d.DOC_NAME like kw.Word).

David Faizulaev Over a year ago

I'm quite new to SQL and especially dynamic SQL. The App's logic is running on Java, so the idea is the input string will be divided into words and we will query the database like you suggested. But given that the string length is unknown, I will have to learn dynamic SQL

|

Ike Walker · Accepted Answer · 2014-11-11 21:49:29Z

4

There are multiple options to implement fulltext search in MySQL. The native way is to add a fulltext index, which can then be used to run searches like your example:

alter table docs
  add fulltext index doc_name_fti (doc_name);

select * 
from docs
where match(doc_name) against ('+virus +spyware' in boolean mode);

There are also products like Sphinx and Solr you can use.

answered Nov 11, 2014 at 21:49

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Comments

hd1 · Accepted Answer · 2014-11-11 21:50:13Z

0

SELECT doc_name FROM docs WHERE doc_name RLIKE '.*virus*spyware'

I find the LIKE syntax less familiar than PCRE.

However, this is probably more performant

SELECT FIELD('virus', doc_name) or FIELD('spyware', doc_name) from docs

answered Nov 11, 2014 at 21:50

hd1

34.8k5 gold badges83 silver badges95 bronze badges

Comments

Bacon Bits · Accepted Answer · 2014-11-11 21:51:07Z

0

Fulltext indexing is the best way to achieve this. If for whatever reason you can't and must use LIKE, then for an ALL terms search:

select * from docs where DOC_NAME like '%virus%' and DOC_NAME like '%spyware%';

For an ANY terms search:

select * from docs where DOC_NAME like '%virus%' or DOC_NAME like '%spyware%';

answered Nov 11, 2014 at 21:51

Bacon Bits

32.4k6 gold badges63 silver badges78 bronze badges

Collectives™ on Stack Overflow

SQL query - match any part of string

4 Answers 4

6 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Related