MySQL - Requires result of finding maximum matching letters from string
Hi I am writing my own MySQL query where I need the result of the records as follows.
The word in the table is ABC XYZ
My string is ABC XYZQWER
when i executed my request as shown below -
SELECT * FROM myTABLE where `column` LIKE 'ABC XYZQWER%';
I am getting an empty result. I know MySQL LIKE matches the result of the row.
I need a way to figure this out.
I searched for it using "ABC X" - it gives me the correct result.
source to share
You can use the function LOCATE()
:
SELECT `column`
FROM myTable
WHERE LOCATE(`column`, 'ABC XYZQWER') = 1;
As long as column
there is a value in the column with the name ABC XYZ
, the query result will be at least:
+---------+
| column |
+---------+
| ABC XYZ |
+---------+
Finding an internal match
Finding a matching string of type 'BC'
within a search string is 'ABC XYZQWER'
possible using the comparison operator >=
. So the WHERE clause would look like this:
WHERE LOCATE(`column`, 'ABC XYZQWER') >= 1;
source to share
This is because you don't have a job with QWER. You are actually looking for a word that does not exist. This way you get a null result.
For example,
Word: qwertyuiuioo search String: qwerty
select * from table where word like qwerty% will give you the result. % accepts any number of characters after the letters you specify that do not match any value in the table.
source to share
Here are some examples of how you can use the 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 Search as described here ( Levenshtein Distance MySQL Function ). But note that this works slightly different from the LIKE clause in such a way that it gives a result based on the distance you specify to 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; he will also give other words that fall within this range.
source to share