MySQL - Need search result of maximum matching letters from a string

Question

Hi I am writing my own MySQL query where I need a result of records as follows.

Word in a table - ABC XYZ

My string - ABC XYZQWER

when I ran my query as below -

SELECT * FROM myTABLE where `column` LIKE 'ABC XYZQWER%';

I am getting empty result. I am aware of the fact that MySQL LIKE matches the result of string.

I need a way to figure this out.

I I searched it using 'ABC X' - it is giving me a proper result.

Answer 1

You can use the function LOCATE():

SELECT `column` 
FROM myTable
WHERE LOCATE(`column`, 'ABC XYZQWER') = 1;

As long as there is a value ABC XYZ in the column named column, the result of the query will be at least:

+---------+
| column  |
+---------+
| ABC XYZ |
+---------+

---

Finding an inner match

Finding a matching string like 'BC', which is inside the search string 'ABC XYZQWER', is possible by using the compare operator >=. So the WHERE clause will look like this:

WHERE LOCATE(`column`, 'ABC XYZQWER') >= 1;