I was looking for the best match through google and stackoverflow.

But it's hard to find what I'm looking for, so I write a question.

There are two name columns in my table. (name1, name2)

I want to receive keywords as input and output them in the most similar order among the data including the keywords.

If the user inputs ed, we want the output to be in the order of 'ed', 'Ed Sheeran' and 'Ahmedzidan'. (The order of 'Ed Sheeran' and 'Ahmed Zidan' may vary depending on the matching method.)

We want the word 'ed' to be the most similar and immediately followed by the word 'ed'.

I don't know how to do exact matching.

The above 'ed' is searched even if it is included in either name1 or name2. There is no priority between the two.

The method I am using now is below.

select
((LENGTH(name1) - LENGTH(( 'ed'))))   ((LENGTH(name) - LENGTH(( 'ed'))))  as score
from user
where name like '