Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: What is a reverse version of LIKE?

Tags:

sql

mysql

LIKE operator in MySql is used to find rows that contain our query text, for example:

select name from user where name like "%john%" 

which will return John Smith, Peter Johnson etc.

What if I need the opposite - to find rows that are CONTAINED in our query text? For example I give it John Smith and Peter Johnson are best friends and want it to find me all names from the table that could be found in this string.

How to do that?

like image 966
serg Avatar asked Jan 23 '09 06:01

serg


People also ask

How use like and not like in MySQL?

MySQL NOT LIKE is used to exclude those rows which are matching the criterion followed by LIKE operator. Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

What is the syntax for like in MySQL?

The MySQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

How do I query an array in MySQL?

Following is an elementary syntax structure to code for MySQL WHERE IN Array command in MySQL server to fetch information using array values and WHERE IN clause: SELECT ColumnName1, ColumnName2, …., ColumnNameNFROM TableNameWHERE ColumnName1 IN(ColumnName1_Value1, ColumnName1_Value2, ColumnName1_Value3);

How do you reverse a row in SQL?

The SQL ORDER BY Keyword The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


2 Answers

Here's a way you can achieve what you describe:

SELECT name FROM user  WHERE 'John Smith and Peter Johnson are best friends' LIKE   CONCAT('%', name, '%') 
like image 110
Bill Karwin Avatar answered Sep 28 '22 17:09

Bill Karwin


select name from users where instr('John Smith and Peter Johnson', name) > 0 

I would rather use this method instead of:

select * from users WHERE  'John Smith and Peter Johnson' LIKE CONCAT('%', name ,'%') 

because if there is any chance that the name could contain the % or _ character (eg. name='v%alue') then the above query would still return that record incorrectly. Also note that if your column could contain backslashes and/or "C escape characters" then they would also need to be escaped. This is all explained in MySQL String Comparison Functions. For example the below SQL would return 1:

SELECT 'val\%ue' LIKE CONCAT('%', 'al\\\\\%u' ,'%'); 

The single backslash needed to be escaped with \\\\ and the % character was escaped: \%.

like image 28
joshweir Avatar answered Sep 28 '22 17:09

joshweir