All Products
Search
Document Center

Other functions

Last Updated: Jun 18, 2021

COALESCE

Declaration

COALESCE(expr, expr, expr,...)

Description

This function evaluates expression values in sequence until the function finds a non-NULL value. Then, the function returns the non-NULL value. If the values of all the expressions are NULL, the function returns a NULL value.

All expressions must be in the same data type. If the data types of expressions are different, the system converts the data types into the same type in an implicit way.

Example

Oceanbase>SELECT COALESCE(NULL,NULL,3,4,5), COALESCE(NULL,NULL,NULL);
+---------------------------+--------------------------+
| COALESCE(NULL,NULL,3,4,5) | COALESCE(NULL,NULL,NULL) |
+---------------------------+--------------------------+
|                         3 |                     NULL |
+---------------------------+--------------------------+
1 row in set (0.00 sec)

NVL

Declaration

NVL(str1,replace_with)

Description

This function returns the replace_with value if the str1 value is NULL.

If you set str1 to NULL, the function returns a value that you set for the replace_with parameter. This helps you retrieve complete outputs. In most cases, the str1 value is a column name. No limits are placed on the values of replace_with. For example, you can specify hard-coded values, references to other columns, or expressions for the replace_with parameter.

Example

Oceanbase>SELECT NVL(NULL, 0), NVL(NULL, 'a');
+--------------+----------------+
| NVL(NULL, 0) | NVL(NULL, 'a') |
+--------------+----------------+
|            0 | a              |
+--------------+----------------+
1 row in set (0.00 sec)

SLEEP

Declaration

SLEEP(duration)

Description

This function suspends an SQL query for a specified duration that is measured in seconds. The function returns 0 after the suspension ends.

If only the SLEEP function is run in a query that is not interrupted, the function returns 0.

If only the SLEEP function is run in a query that is interrupted, the function returns 1 and does not return an error code.

If the SLEEP function is part of a query and the query is interrupted during the suspension, the function returns error code 1317.

Example

mysql> SELECT SLEEP(1000);
+------------------+
| SLEEP(1000) |
+------------------+
|          0 |
+------------------+

mysql> SELECT SLEEP(1000);
+------------------+
| SLEEP(1000) |
+------------------+
|          1 |
+------------------+

mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
ERROR 1317 (70100): Query execution was interrupted

Full-text search functions

Declaration

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

Description

ApsaraDB for OceanBase V1.0 supports full-text search functions. You can use such functions to query full-text indexes. Before you use full-text search functions, ensure the following requirements are met.

  • Full-text indexes are created on the columns that are specified in the MATCH(col1,col2,...) function. ApsaraDB for OceanBase supports only FULLTEXT CTXCAT indexes.

  • The MATCH(col1,col2,...) function must include the columns that are specified for the FULLTEXT CTXCAT indexes. For example, you can create the FULLTEXT INDEX(c1,c2,c3) CTXCAT(c2,c3) index. In this scenario, the retrieved data can be matched only if the function is MATCH(c2,c3).

  • You can specify the search mode for the full-text search function by using the search_modifier parameter. ApsaraDB for OceanBase supports only two modes: NATURAL LANGUAGE MODE and BOOLEAN MODE. The default mode is NATURAL LANGUAGE MODE.

By default or when you use IN NATURAL LANGUAGE MODE, the MATCH...AGAINST statement uses NATURAL LANGUAGE MODE for full-text searches. In NATURAL LANGUAGE MODE, the AGAINST clause accepts a search string and searches in the index by comparing character sets. For each row in the table, the MATCH function returns the correlation between the string and the row data. The correlation represents the similarity between the string and the text in the data table. By default, string columns created in ApsaraDB for OceanBase are case-insensitive. Therefore, keywords for full-text searches are case-insensitive. If you need to run case-sensitive full-text searches, you can specify case-sensitive data types for the columns on which full-text indexes are created. For example, you can specify the data type of the columns as UTF8MB4_BIN. If the MATCH...AGAINST function is used in the WHERE clause, MATCH is used to filter data that is irrelevant to the keywords in the function. ApsaraDB for OceanBase supports only MATCH...AGAINST=0 and MATCH...AGAINST>0. MATCH...AGAINST=0 indicates that no data matches the keywords and MATCH...AGAINST>0 indicates that at least one keyword is matched. The AGAINST parameter can accept multiple keywords. The keywords are separated with spaces, which indicates the logical OR relationship. If one of the keywords matches the text, this is considered as a match.

ApsaraDB for OceanBase can run full-text searches in BOOLEAN mode by using the IN BOOLEAN MODE keyword. In this mode, some special operators in front of the keywords have special semantic meanings. Examples

SELECT * FROM t1 WHERE MATCH (a, b) AGAINST ('Chrysanthemum Jasmine' IN BOOLEAN MODE);
+----+------------+------------+
| id | a            | b            |
+----+------------+------------+
| 1 | Alipay        | Chrysanthemum tea        |
| 2 | Taobao          | Jasmine        |
+----+------------+------------+

SELECT * FROM t1 WHERE MATCH (a, b)
    AGAINST ('+Chrysanthemum -Jasmine' IN BOOLEAN MODE);
+----+------------+------------+
| id | a            | b            |
+----+------------+------------+
| 1 | Alipay        | Chrysanthemum tea        |
+----+------------+------------+

The BOOLEAN full-text search in ApsaraDB for OceanBase supports the following operators: Plus signs (+) represent logical AND relationships. The search result must also include the keywords that are preceded by a plus sign (+). Hyphens (-) represent logical NOT relationships. The search result cannot include the keywords that are preceded by a hyphen (-).

If no operator is specified, the logical OR relationship is applied to the specified keywords. This indicates that the search result includes at least one of the specified keywords that are not preceded by an operator.

In BOOLEAN mode, take note of this point: All operators must be placed in front of the keywords, and operators behind the keywords are ignored. For example, the plus sign (+) in "+Chrysanthemum" has a semantic meaning, but is ignored in "Chrysanthemum+". Operators and keywords must be connected in a close way and cannot be separated by other characters. Otherwise, the operators are ignored. For example, the plus sign (+) in "+ Chrysanthemum" is ignored.