CODEWITHSUNDEEP
mysqlsql
Tom
Tom

Reputation: 936

SQL find words in string, sort result by number of words found

I have a coding problem and was wondering if there is a SQL implementation of the following scenario:

I want to search in a text column for certain words and want to sort the results based on the number of individual words found. For example:

Let's find the row which contains: a b s

a b b c d e s
b d s w d a s
x d s g w d s
f e h w d s a

The desired result would be: 

a b b c d e s (it contains all 3 words)
b d s w d a s (it contains all 3 words)
f e h w d s a (it contains 2 words)
x d s g w d s (it contains 1 word)

Is it recommended doing something like this rather in e.g. PHP or is there an efficient SQL way to do so?

Upvotes: 1

Views: 359

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can do this using boolean expressions:

select t.*,
       ( (col like '%a%') +
         (col like '%b%') +
         (col like '%s%') +
       ) as num_matches
from t
order by num_matches desc;

If words should be separated by spaces, then:

select t.*,
       ( (concat(' ', col, ' ') like '% a %') +
         (concat(' ', col, ' ') like '% b %') +
         (concat(' ', col, ' ') like '% s %') +
       ) as num_matches
from t
order by num_matches desc;

Finally, if you have this type of problem, then you should do one of two things:

  • If the text really is text, then look into full text search capabilities.
  • If the text is really a list of something like keywords or users, then fix your data model and use a junction/association table.

Upvotes: 1

Grzesiek Danowski
Grzesiek Danowski

Reputation: 467

My idea:

  1. convert to distinct words list using solution: SQL split values to multiple rows
  2. count found rows for every original row.

Upvotes: 0

Jamie Lupton
Jamie Lupton

Reputation: 118

If done within SQL, I would look at doing it using full text search which allows for RANK(http://msdn.microsoft.com/en-us/library/cc879245.aspx)

Upvotes: 0

Related Questions