All Products
Search
Document Center

Other functions

Last Updated: Jan 04, 2020

COALESCE

Syntax

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

Description

The function evaluates the expressions in the parameters consecutively until a non-NULL value is encountered. The function stops and returns the value if all expressions are NULL, it returns a NULL value.

All expressions must be the same type or can be implicitly converted to the same type.

Example:

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

NVL

Syntax

NVL(str1,replace_with)

Description

If str1 is NULL, then replace it with replace_with.

Whenever you give it a null value, it returns a value of your choice. The ability to automatically replace null values allows a more complete output. str1 is often a column name. replace_with can be any value, such as a direct value (hard-coded), a reference to another column, or an expression.

Example:

  1. Oceanbase>SELECT NVL(NULL, 0), NVL(NULL, 'a');
  2. +--------------+----------------+
  3. | NVL(NULL, 0) | NVL(NULL, 'a') |
  4. +--------------+----------------+
  5. | 0 | a |
  6. +--------------+----------------+
  7. 1 row in set (0.00 sec)

SLEEP

Syntax

SLEEP(duration)

Description

The SLEEP function pauses for the specified duration in seconds and returns 0 after the pause ends.

If SLEEP is executed without interruption, it returns 0.

If the execution of SLEEP is interrupted, the result 1 is returned, and no error code is returned.

If SLEEP is part of the query and is interrupted during the pause, an error code 1317 is returned.

Example:

  1. mysql> SELECT SLEEP(1000);
  2. +------------------+
  3. | SLEEP(1000) |
  4. +------------------+
  5. | 0 |
  6. +------------------+
  7. mysql> SELECT SLEEP(1000);
  8. +------------------+
  9. | SLEEP(1000) |
  10. +------------------+
  11. | 1 |
  12. +------------------+
  13. mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
  14. ERROR 1317 (70100): Query execution was interrupted

Full-text search functions

Syntax

  1. MATCH (col1,col2,...) AGAINST (expr [search_modifier])
  2. search_modifier:
  3. {
  4. IN NATURAL LANGUAGE MODE
  5. | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  6. | IN BOOLEAN MODE
  7. | WITH QUERY EXPANSION
  8. }

Description

ApsaraDB for OceanBase 1.0 supports the use of full-text search functions to query full-text indexes. The use of full-text search functions has the following requirements.

  • Input columns of the MATCH (col1, col2, …) function must include full-text indexes (ApsaraDB for OceanBase only supports FULLTEXT indexes).
  • For the FULLTEXT indexing, the input columns of MATCH (col1, col2, …) must completely cover the columns of the indexes. For example, only MATCH(c1, c2, c3) can match FULLTEXT INDEX(c1, 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 NATURAL LANGUAGE MODE is used by default.

By default or when you specify the IN NATURAL LANGUAGE MODE keyword, the MATCH…AGAINST statement uses the NATURAL LANGUAGE mode for full-text search. In the NATURAL LANGUAGE mode, the AGAINST clause accepts a search string and searches in the index based on character set comparisons. For each row of data in the table, the MATCH function returns the correlation between the string and the data in the row. 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, so search strings for full-text searches are case-insensitive. If you want the search string to be case-sensitive, you can specify a case-sensitive data type for the column for full-text indexes, such as UTF8MB4_BIN.

If the MATCH…AGAINST function is used in a WHERE clause, MATCH is used to filter data that is not related to the search string. ApsaraDB for OceanBase only supports MATCH…AGAINST=0 and MATCH…AGAINST>0. MATCH…AGAINST=0 means that no data is similar to the search string and MATCH…AGAINST>0 means at least one text is similar to the search string.

The AGAINST parameter can accept multiple search strings. The search strings are separated with spaces, indicating the logical OR relationship. As long as one of the search strings matches the text, it is considered as a match.

ApsaraDB for OceanBase can perform full-text searches in BOOLEAN mode by using the IN BOOLEAN MODE keyword. In this mode, some special operators in front of search strings have special semantics. Example:

  1. SELECT * FROM t1 WHERE MATCH (a, b) AGAINST ('Chrysanthemum Jasmine' IN BOOLEAN MODE);
  2. +----+------------+------------+
  3. | id | a | b |
  4. +----+------------+------------+
  5. | 1 | Alipay | Chrysanthemum Tea |
  6. | 2 | Taobao | Jasmine |
  7. +----+------------+------------+
  8. SELECT * FROM t1 WHERE MATCH (a, b)
  9. AGAINST ('+Chrysanthemum -Jasmine' IN BOOLEAN MODE);
  10. +----+------------+------------+
  11. | id | a | b |
  12. +----+------------+------------+
  13. | 1 | Alipay | Chrysanthemum Tea |
  14. +----+------------+------------+

The BOOLEAN full-text search of ApsaraDB for OceanBase currently supports the following operators:

  • Plus signs (+) represent logical AND relationships and indicate that the search result must also include the search strings that are preceded by a plus sign (+).
  • Hyphens (-) represent negation and indicate that the search result must exclude the search strings that are preceded by a hyphen (-).
  1. No operator indicates a logical OR relationship. This means that the search result must include at least one string that is not preceded by operators.

Take note of the following points in the BOOLEAN mode:

  • All operators must be placed in front of the keyword. The operator after the keyword is ignored. The plus sign (+) has semantic meaning in “+chrysanthemums”, but is ignored in “chrysanthemums+”.
  • Operators and strings must be closely connected and cannot be separated by other characters. Otherwise, the operators are ignored. For example, the plus sign (+) in “+ chrysanthemums” is ignored.