MySQL Select Query String Matching

MySQL SELECT query string matching

Just turn the LIKE around

SELECT * FROM customers
WHERE 'Robert Bob Smith III, PhD.' LIKE CONCAT('%',name,'%')

Checking for string match with a MySQL query

 $query_posts= "SELECT * FROM posts WHERE user_id= '$user_id' AND story LIKE '%$filter_tag%' ORDER BY post_id 
    DESC";

MySQL query String contains

Quite simple actually:

SELECT *
FROM `table`
WHERE `column` LIKE '%{$needle}%'

The % is a wildcard for any characters set (none, one or many). Do note that this can get slow on very large datasets so if your database grows you'll need to use fulltext indices.

MySQL query to SELECT rows with LIKE and create new column containing the matched string

Without a separate regex library, you'll need to use the built-in string functions to find the location of the match, and then extract the matching sub-string:

SELECT 
  id, 
  name, 
  substring(name, locate('app', name), length('app')) as new_column 
FROM yourTable 
WHERE name LIKE '%app%'

Which gives the results:

+----+-------------+------------+
| id |    name     | new_column |
+----+-------------+------------+
|  1 | Green Apple | App        |
|  3 | application | app        |
+----+-------------+------------+

Sql Fiddle Here

MySQL Selecting From a Table name matching string

You can't use an expression for the table name in SQL. You need to use a stored procedure that creates dynamic SQL and executes it using PREPARE and EXECUTE.

SET @sql = CONCAT('SELECT * FROM XXXX.xxx_', DATE_FORMAT(NOW(), '%y_%m'));
PREPARE stmt FROM @sql;
EXECUTE stmt;

A database design that requires this seems like a poor decision. You should have a single table where the date is in a column, not separate tables for each month.

If you're running the query from a programming language, you can use its own date functions to constructure SQL.

MySQL search match at least one word on query string

IN method

Use comma separated list of your search query:

SELECT * FROM am_ciudad WHERE Ciu_Nombre IN('North', 'Washington', ...)

REGEXP method

I can imagine the REGEXP will be slower, but I haven't benchmarked it.

SELECT * FROM am_ciudad WHERE Ciu_Nombre REGEXP(North|Washington|...)

String Pattern Matching MySQL

You can do it using MySQL full Text search:

Here you don't need to break the bigger input string into an array to compare with each individual word.

Please refer the below examples to write your own:

I want to explain you about Boolean Full Text Search; But I advise you to please go through Full Text Search using Query Expansion also.

Let's look at the example table:

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial   | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"database comparison"' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

Order matters, when the words are quoted:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"comparison database"' IN BOOLEAN MODE);

Empty set (0.01 sec)

When we remove the quotes, it will search for rows, containing words "database" or "comparison":

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('database comparison' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

Order doesn't matter now:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('comparison database' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

If we want to get rows, containing either word "PostgreSQL" or phrase "database comparison", we should use this request:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

fiddle

Make sure, that the words, you are searching for, are not in the list of stopwords, that are ignored.

MySQL - matching and non-matching results in Where IN query

You can try below - using cte

with cte1 as 
(
select 'abc' as key
union 
select 'pqr' union select 'xyz' 
)
select keyword,case when key is null then 0 else 1 end as is_present 
from keywords left join cte1 on keyword=key

Return the name of the MySQL column where a select match was

You could use CONCAT_WS here:

SELECT *,
    CONCAT_WS(',', IF(phone = :query, 'phone', NULL),
                   IF(email = :query, 'email', NULL),
                   IF(facebook = :query, 'facebook', NULL),
                   IF(instagram = :query, 'instagram', NULL)) AS matching_cols
FROM profiles
WHERE -- your conditions here

This would return a CSV string, consisting of up to 4 columns, which matched the incoming :query string parameter.

---

Related Topics