Mysql - select match with most characters

Question

I don't know which function is being called, but I'm trying to select a string that matches / has most of the characters to output. Here is what I have tried.

select * from table where title like '%alotofcharactershere%' limit 1 

Above all, works only for the whole word: alotofcharactershere

What I really want is to split it into: alotofcharactershere and find a string containing the most suitable characters.

Please help if you know what I'm talking about. Thank you in advance.

Answer 1

I was digging around, and I'm not an expert on SQL, but after replacing characters that don't match, I thought of order strings of length char.

Something like: SELECT * FROM your_table ORDER BY LENGTH(REGEXP_REPLACE(title, '[^abcdefg]', ''));

2 problems. It does not match the most various characters found. Thus, aaaa will have higher value than abc. And I think you mean that you want abc to be more valuable. The second problem, probably the biggest one, mySQL does not support this regex. And not sure what is doing something. But I found this article: https://launchpad.net/mysql-udf-regexp

Although I think that there should be something much simpler and something that actually works out of the box, this answer may just prompt someone to think out of the box.

There may also be a solution with a lot of connections, but this is most likely slower than selecting everything and sorting it in php (or similar)

Answer 2

It is not clear what you are trying to accomplish, so I wonder if you looked at Full Text Searches . Being able to find relevance may give you some ideas or alternatives.