CODEWITHSUNDEEP
mysqlsqlregexpattern-matching
Wilo Maldonado
Wilo Maldonado

Reputation: 551

String Pattern Matching MySQL

I am trying to figure out the best way to query a MySQL DB under this scenario. Lets imagine I have a Table with personal information for the user. The field I want to query is the Description

Column Contents:

[1] John Loves to play music
[2] John hates music
[3] David enjoys listening to music
[4] Michael enjoys dancing.

I have an input field in the form where a user will enter "John music" and it should return the first two results.

I am currently using this query:

SELECT * from TABLE WHERE description LIKE '%$input_field%'

This clearly does not work for me because it will only return the results that contain "John music" together in the String.

How can I achieve what I want?

UPDATE:

A second scenario would be how to obtain all the results that have John or Music in them. That would return the first THREE results.

How can I do this?

Upvotes: 2

Views: 739

Answers (2)

Tushar
Tushar

Reputation: 3623

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.

Upvotes: 2

Richard
Richard

Reputation: 30618

For the most flexibility, you should look at MySQL Full Text Functions. However, if you want basic SQL answers...

If you want to find occurrences where John occurs before music...

WHERE description LIKE '%john%music%'  # (i.e. change the values of your parameter)

If you want to find occurrences containing John and Music (in either order)...

WHERE description LIKE '%john%' AND description LIKE '%music%' 
    # (i.e. you need two input parameters)

If you want to find occurrences containing John OR music...

WHERE description LIKE '%john%' OR description like '%music%'

Upvotes: 0

Related Questions