tom12e tom12e - 3 months ago 21
SQL Question

Find similarities with datastore

Let's say a user can input multiple strings, server processes it and stores it inside datastore. Is it possible query on the number of similarities (without any work around)? Or is there a beter solution to this?

Example Query: get id where value similair to "a, b, c"

Returns: 1, 8, 9, 6, 10, etc..

Example data:

enter image description here

Answer

You should fix your data structure so you have one row per id and value. String manipulation functions are not particularly good in SQL.

With you data, you can do this:

select t.*,
       ((case when ',' || value || ',' like '%,a,%' then 1 else 0 end) +
        (case when ',' || value || ',' like '%,b,%' then 1 else 0 end) +
        (case when ',' || value || ',' like '%,c,%' then 1 else 0 end)
       ) as nummatches
from t
order by nummatches desc;

The concatenation operator varies among databases; it might be +, &, or concat().