SQL MATCH AGAINST operator for text

Hello! In this tutorial I am going to talk about the MATCH AGAINST command or operator.

This SQL operator will allow you to perform more advanced searches in your databases.

In addition, I leave you several examples that make their use clear.

ohDon’t forget your restrictions! I leave them to you at the end.

Content

  • Definition and syntax
    • What is it for?
  • Examples of use
    • Example with SELECT
    • Example with UPDATE
    • Example with INSERT
  • Relevance in MATCH AGAINST
    • Example of MATCH AGAINST + ORDER BY + relevance
  • Related content

Definition and syntax

The MATCH AGAINST command is used to perform full-text searches on one or more columns of a table.

The basic syntax is as follows:

SELECT * 
FROM tabla 
WHERE MATCH(columna1,[columnaN]) AGAINST ('busqueda');

In this syntax, “table” is the name of the table you want to search for, “column” is the name of the column or columns you want to search in, and “search” is the word or phrase you want to search for.

You can use various operators and options to customize your search.

Also, very importantly, I have indicated [columnaN] because you can indicate so many columns of comparable text as you need.

What is it for?

The main utility of this operator in you want Query SQL is usually to improve the typical LIKE SQL.

Although you may think that they are similar, LIKE looks for simple patterns and when we want to compare phrases or even words that are skipped or out of order, the query configuration becomes impossible.

This is where MATCH AGAINST helps us with the problem since it offers even the relevance of the comparison. I will explain the latter later.

Examples of use

Here are some examples of how to use the MATCH AGAINST command in SELECT, UPDATE, and INSERT queries.

Example with SELECT

Suppose you have a table called “products” that contains information about the products in an online store, including their name, description, and price.

You want to search for products that contain the word “shirt” in their name or description.

The SELECT query would be like this:

SELECT *
FROM productos 
WHERE MATCH(nombre, descripcion) AGAINST ('camiseta');

In this example, as you can see, I have used multiple columns in the MATCH, in this way, I can check the word or phrase to search in multiple columns.

Example with UPDATE

Now suppose you want to do an UPDATE query with MATCH AGAINST to update the price of all products that contain the phrase “striped t-shirt” in their description.

The UPDATE query would be like this:

UPDATE productos 
SET precio = precio * 0.9 
WHERE MATCH(descripcion) AGAINST ('camiseta a rayas');

Example with INSERT

In INSERT SQL type queries you can not use MATCH AGAINST.

Because?

Because this query does not compare texts, therefore the operator will have no place to use it.

Relevance in MATCH AGAINST

Once I have already explained the basic part, I would like to show you something additional that undoubtedly makes MATCH AGAIN the perfect algorithm for text web searches.

I am talking about the “relevance”, that is, the results obtained for a search, what is the degree of relevance of each one.

This is resolved by including the MATCH AGAINST in the SELECT.

Example of MATCH AGAINST + ORDER BY + relevance

SELECT id, titulo, MATCH(titulo) AGAINST ('tutorial de SQL fácil') as relevancia
FROM post
WHERE MATCH(titulo) AGAINST ('tutorial de SQL fácil')
ORDER BY relevancia DESC

Related content

Here are some topics related to the MATCH AGAINST command that may interest you:

  • Search with the LIKE operator in SQL: If you want to search for patterns instead of exact words, you can use the LIKE operator instead of MATCH AGAINST.
  • Full text indexes: To improve the performance of your full-text searches, you can create full-text indexes on the columns of your table. Full-text indexes allow for faster and more accurate searches.
  • Searches with regular expressions: If you need to perform even more complex searches, you can use regular expressions instead of MATCH AGAINST.

I hope this tutorial has been useful to you. If you have any questions or suggestions, feel free to leave a comment. Until next time!

Leave a Reply