MATCH

MySQL

MATCH(column1,column2,…) AGAINST (expression [search_modifier])

• match (column1,column2,…): Specifies the columns where the search is to be performed. The columns must be part of a FULLTEXT index. Multiple columns can be provided, separated by commas.
• against (expression): Defines the string to search for within the columns specified in the MATCH function. The expression is usually a string that is enclosed in single or double quotes.
• search_modifier: An optional parameter that determines the mode of the match. Possible values for the search modifier include IN NATURAL LANGUAGE MODE, IN BOOLEAN MODE, WITH QUERY EXPANSION, IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION, and IN BOOLEAN MODE WITH QUERY EXPANSION. Without a search_modifier, the query performs a natural language search.

Example

SELECT *
FROM Employee
WHERE MATCH (name, position) AGAINST ('John Doe');

Output

+----+-----------+----------------+
| ID |   name    |    position    |
+----+-----------+----------------+
|  1 | John Doe  | Product Manager|
+----+-----------+----------------+

Explanation

The MATCH statement in SQL is utilized for full-text searching. The specified MATCH (name, position) AGAINST ('John Doe') command matches rows in the Employee table where either the name or position column contains ‘John Doe’. The selected row is returned in the output.

SQL Server

MATCH(column_name [, …]) AGAINST (expression [search_modifier])

• column_name: This parameter refers to the column that will be searched. It specifies the columns where the search is going to be executed. In some cases, you may specify multiple columns separated by commas.
• expression: This is the value that is to be searched in the specified column. It represents the string expression against which the search is to be conducted.
• search_modifier: This is an optional parameter. It’s used to refine the search further. It can take values like ‘IN NATURAL LANGUAGE MODE’, ‘IN BOOLEAN MODE’, or ‘WITH QUERY EXPANSION’ to adjust the search according to your needs.

Example

SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "chocolate" ');

Output

ProductName
-----------
"Delicious Chocolate"
"Chocolate Truffles"
"Premium Chocolate Bar"

Explanation

The MATCH statement in SQL Server is used within the WHERE clause. It enables search for rows that match specific criteria related to full-text search queries. In this particular example, the SQL statement is searching for all product names containing the word ‘chocolate’. The output displays all product names within the Products table that meet this criterion.

Oracle

MATCH_RECOGNIZE( PARTITION BY column1, column2, … ORDER BY column3, column4, … MEASURES match_measure1, match_measure2, … ONE ROW PER MATCH|ALL ROWS PER MATCH AFTER MATCH SKIP TO NEXT ROW|AFTER MATCH SKIP PAST LAST ROW PATTERN (pattern) DEFINE condition_definition1, condition_definition2, …)

• partition by column1, column2, …: This parameter is used to divide the rowset into partitions, grouping them based on one or multiple columns. Each partition is processed independently for pattern recognition.
• order by column3, column4, …: This is used to specify the order of records within each partition. The sequence is significant in pattern recognition as the sequence of rows is essential for identifying patterns.
• measures match_measure1, match_measure2, …: This defines measures that will be included in the output. These measures are usually calculations based on columns in the row pattern output table.
• one row per match|all rows per match: This parameter determines the output mode. ‘ONE ROW PER MATCH’ returns one row for each match. ‘ALL ROWS PER MATCH’ returns all rows that were part of the match; each row will contain the same match number.
• after match skip to next row|after match skip past last row: This decides where to restart pattern matching after finding a match. ‘AFTER MATCH SKIP TO NEXT ROW’ restarts from the row after a match’s starting row. ‘AFTER MATCH SKIP PAST LAST ROW’ skips to the row following the entire match.
• pattern (pattern): This defines the regular expression pattern which will be used for pattern matching. This pattern will be matched against the rows of each partition.
• define condition_definition1, condition_definition2, …: This is used to define conditions that specify variables in the pattern. Each variable represents a subset of rows within the overall pattern match.

Example

SELECT employee_id, first_name, last_name
FROM employees
WHERE MATCHES(last_name, 'Doe');

Output

| EMPLOYEE_ID | FIRST_NAME | LAST_NAME |
|-------------|------------|-----------|
| 1002        | John       | Doe       |

Explanation

In the SQL query provided, the MATCHES function is used to filter rows in the employees table where the last_name is ‘Doe’. The function returns any rows where the last_name matches the specified string ‘Doe’. In the output, you will see the employee_id, first_name, and last_name of any employee where the last_name was a match.