1

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?

Tom
  • 906
  • 1
  • 8
  • 30

3 Answers3

1

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks - works exactly as expected. Also great idea with the spaces between the words! – Tom Oct 29 '18 at 16:48
0

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)

Jamie Lupton
  • 118
  • 8
0

My idea:

  1. convert to distinct words list using solution: SQL split values to multiple rows
  2. count found rows for every original row.
Grzesiek Danowski
  • 437
  • 3
  • 10
  • Sorry, but I'm newbie on stackoverflow. Why should comment not answer? I can see caption: "Comments are used to ask for clarification or to point out problems in the post." My text is ask or clarification? – Grzesiek Danowski Oct 29 '18 at 15:26
  • more or less like "This was posted as an answer, but it does not attempt to answer the question. It should possibly be an edit, a comment, another question, or deleted altogether. " – Raymond Nijland Oct 29 '18 at 15:28
  • Ok, I understand - my solution is not enough detailed. – Grzesiek Danowski Oct 29 '18 at 15:33