MySQL - Need search result of maximum matching let

问题:

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.

回答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;

回答2:

It is because you dont have a work which has QWER. You are actually searching for a word which is not present. So you are getting a zero result.

For eg:

Word : qwertyuiuioo search String : qwerty

select * from table where word like qwerty% you will get the result. % takes any number of characters after the letters you have given which is not matching any value in the table.

回答3:

Try this:

SELECT * FROM myTABLE a WHERE 'ABC XYZQWER' LIKE CONCAT(a.column, '%');

回答4:

Here are some examples of how one might use LIKE clause in SQL queries:

SELECT * FROM myTABLE where column LIKE 'ABC%';// matches ABCD, ABC D but not DABC

SELECT * FROM myTABLE where column LIKE '%ABC%';// matches any string that contains ABC anywhere in the string eg. DABC, D ABC but not D AB C

for your case you would do something like this:
SELECT * FROM myTABLE where column LIKE 'ABC XYZ%';

You won't be able to do perfect substring searches although you can apply Levenshtein distance searches as described here (Levenshtein Distance MySQL Function). But do note these work a bit differently from LIKE clause in a way that it gives you the result based on the distance you specify for a search.

And after that you can use it like this:

SELECT * FROM mytable WHERE levenshtein("ABC XYZQWER",column) <= 4 

This will give you the result set you are looking for; it will also give other words which fall under this range.