Using full-text search

You can use search functions to find character data (text) and IPv4 addresses in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. This function searches the text in specified columns or strings based on a list of given search terms. The function returns TRUE if the text matches the specified search terms based on the search semantics.

In most cases, you call the SEARCH function by specifying it in the SELECT list or the WHERE clause of a SELECT statement. If the function is used as a WHERE clause filter, the query returns rows when the function returns TRUE.

The SEARCH function requires no setup and no additional privileges. If you’re using a role that has the privileges to access the data in a column, you can search for that data by using the SEARCH function.

The next sections contain more information about the SEARCH function and about optimizing query performance when you use it:

Using the SEARCH function

The SEARCH function finds character data (text) in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns.

When you use the SEARCH function, a text analyzer breaks the text into tokens, which are discrete units of text, such as words or numbers. A default analyzer is applied if you don’t specify one. The analyzer extracts tokens from both the search terms and the data.

If tokens extracted from the search terms match tokens extracted from a specified column or field according to the search semantics, the function returns TRUE. The SEARCH_MODE function argument specifies either 'OR' (disjunctive) or 'AND' (conjunctive) for the search semantics:

  • When 'OR' (the default) is specified, there is a match if any of the tokens extracted from the columns or fields being searched match any of the tokens extracted from the search string. For example, if the search_string value is 'blue red green', the function returns TRUE for a row that contains blue OR red OR green in any of the columns or fields being searched.

  • When 'AND' is specified, there is a match if the tokens extracted from at least one of the columns or fields being searched match all of the tokens extracted from the search string. The matching tokens must all be in one column or field; they can’t be spread across multiple columns or fields. For example, if the search_string value is 'blue red green', the function returns TRUE for a row that contains blue AND red AND green in at least one of the columns or fields being searched.

The following example searches for the string snow leopard in the text leopard with the default SEARCH_MODE ('OR') and the default analyzer:

SELECT SEARCH('leopard', 'snow leopard');
Copy
+-----------------------------------+
| SEARCH('LEOPARD', 'SNOW LEOPARD') |
|-----------------------------------|
| True                              |
+-----------------------------------+

The following example searches for the string snow leopard in the text lion:

SELECT SEARCH('lion', 'snow leopard');
Copy
+--------------------------------+
| SEARCH('LION', 'SNOW LEOPARD') |
|--------------------------------|
| False                          |
+--------------------------------+

The following example searches for the string snow leopard in the text leopard and specifies 'AND' for the SEARCH_MODE argument:

SELECT SEARCH('leopard', 'snow leopard', search_mode => 'AND');
Copy
+---------------------------------------------------------+
| SEARCH('LEOPARD', 'SNOW LEOPARD', SEARCH_MODE => 'AND') |
|---------------------------------------------------------|
| False                                                   |
+---------------------------------------------------------+

For more information about this function and additional examples, see SEARCH.

Using the SEARCH_IP function

The SEARCH_IP function finds valid IPv4 addresses in specified character-string columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. The search is based on a single IP address that you specify. If this IP address exactly matches an IP address in the specified column or field, the function returns TRUE.

The following example searches for the IP address 10.10.10.1 in the text 192.0.2.146:

SELECT SEARCH_IP('192.0.2.146','10.10.10.1');
Copy
+---------------------------------------+
| SEARCH_IP('192.0.2.146','10.10.10.1') |
|---------------------------------------|
| False                                 |
+---------------------------------------+

For more information about this function and additional examples, see SEARCH_IP.

Optimizing queries that use the SEARCH function

To improve the performance of queries that use the function, you can optionally enable FULL_TEXT search optimization on a specific column or set of columns in a table. When you enable search optimization, a new search access path is built and maintained.

Language: English