1

In a mysql (or mariadb) table:

mytable:
---
id PK Biginteger Autoincrement
name varchar(255)

I search stuff like this:

select * from mytable where name like "%someval%"; 

The searchterm can be in Greek as Well:

select * from mytable where name like "%οτιδήπ%"; 

How I can order the returned results by relevance to the value I search for? I mean once I search for οτιδήπ Ι want the result that maches the name the closer to οτιδήπ to be returned first.

How I can do that?

5
  • use FULL TEXT that would give you a value hoiw good the pattern matches
    – nbk
    Commented Apr 8 at 11:44
  • There is no "relevance" term for LIKE operator. The only possible way is SELECT .. WHERE column LIKE 'literal' .. ORDER BY column = 'literal' COLLATE {collation} DESC;. Collation can be case-sensitive or not, accent-sensitive or not, or even binary - depends on your need.
    – Akina
    Commented Apr 8 at 11:56
  • You need to define what you mean by distance between strings. Commented Apr 9 at 2:30
  • Explain what you mean by "relevance" and "closer".
    – Rick James
    Commented Apr 22 at 0:03
  • With both terms terms I mean the closest resemblance in the word I search for. In my case, if searching for "οτιδήπ", I want the results where the "name" column contains something that closely resembles "οτιδήπ" to be returned first. Same applies with someval as well. Commented Apr 22 at 11:10

1 Answer 1

1

Like has no such information, but Full Text index has it

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,      
  FULLTEXT KEY ( title)
);

And you would serach like this

SELECT 
  id, 
  title
FROM 
  posts 
WHERE 
  MATCH (title) AGAINST (
    'οτιδήπ' IN NATURAL LANGUAGE MODE
  );

There are different MODEs that can also be used if the result is not satisfactory.

so read the manual carefullly.

The value

   MATCH (title) AGAINST (
    'οτιδήπ' IN NATURAL LANGUAGE MODE
  )

gives you a value, which tells you how good the index has found the search terms

3
  • But is Greek supported OOB or need to do some configuration in mysql / mariadb? Commented Apr 8 at 18:51
  • i haven't done greek, so i can not tell you try it out, on dbfiddle for example
    – nbk
    Commented Apr 8 at 20:04
  • Works with Greek OOB. Commented Apr 10 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.