COALESCE
Declaration
COALESCE(expr, expr, expr,...)
Description
This function evaluates a specified series of expressions in order. It returns the first non-NULL value and stops evaluating the rest expressions. If all expressions are NULL, it returns NULL.
All expressions must be of the same type or can be implicitly converted to the same type.
Example
obclient> 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
If the value in str1 is null, this function returns the value specified for replace_with.
Given a NULL value, this function returns the specified value. It enables you to provide better looking output. str1 is generally a column name. replace_with can be any value: a literal (for example, a hard-coded value), a reference to another column, or an expression.
Example
obclient> 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
The SLEEP function pauses for a period that you specified for duration in seconds. It returns 0 when the pause period ends.
- If
SLEEPis separately executed and not interrupted, 0 is returned.
- If
SLEEPis interrupted during separate execution, 1 is returned without any error code.
- If
SLEEPis a part of a query and is interrupted during the pause, it returns the Error 1317.
Example
obclient> SELECT SLEEP(1000);
+------------------+
| SLEEP(1000) |
+------------------+
| 0 |
+------------------+
obclient> SELECT SLEEP(1000);
+------------------+
| SLEEP(1000) |
+------------------+
| 1 |
+------------------+
obclient> SELECT 1 FROM t1 WHERE SLEEP(1000);
ERROR 1317 (70100): Query execution was interrupted
Full-text search function
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
OceanBase Database supports using the full-text search function to search for full-text indexes. Conditions for using the full-text function:
- The columns specified in the full-text search function MATCH(col1,col2,...) must have a full-text index (only FULLTEXT CTXCAT index is supported).
- In the FULLTEXT CTXCAT index, the columns specified in the full-text search function MATCH(col1,col2,...) must completely overwrite the full-text columns in the index. For example, FULLTEXT INDEX(c1, c2, c3), CTXCAT(c2, c3) must be MATCH(c2,c3).
- The full-text search function can use the preceding keywords to specify the search mode. Valid values: NATURAL LANGUAGE MODE and BOOLEAN MODE. Default value: NATURAL LANGUAGE MODE.
The MATCH...AGAINST function uses the NATURAL LANGUAGE mode to perform a full-text search by default or when the IN NATURAL LANGUAGE MODE keyword is specified. In NATURAL LANGUAGE mode, AGAINST accepts a search string and searches in the index using the character set comparison method. For each row of data in the table, the return value of MATCH represents the relevance between the search string and the data in the row, that is, the similarity between the text in the search string and the text in the data table. By default, columns related to the strings created in OceanBase Database are case-insensitive. Therefore, the keywords in full-text search are case-insensitive. To conduct a case-sensitive full-text search, you can specify a case-sensitive data type for the columns with full-text indexes, for example, the UTF8MB4_BIN data type. If the MATCH...AGAINST function is used in a WHERE clause, MATCH is used to filter out data that does not match the keyword relevance.MATCH...AGAINST = 0 indicates that no data similar to the keyword is found. Only MATCH...AGAINST = 0 and MATCH...AGAINST > 0 are supported. The latter indicates that at least one keyword is found relevant to the data. The AGAINST parameter accepts multiple keywords separated with a period (.), indicating the OR relationship. If any keyword is matched, the search condition is met.
You can use the IN BOOLEAN MODE keyword to perform a full-text search in Boolean mode. In this mode, the special operators before the keyword have special semantics. Example:
SELECT * FROM t1 WHERE MATCH (a, B) AGAINST St ('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 OceanBase Database supports the following operators:
The plus sign (+) represents the AND relationship, indicating that the search result must contain the keywords modified by the plus sign (+).
The minus sign (-) represents the NOT relationship, indicating that the search result cannot contain any keywords modified by the minus sign (-).
(no operator) represents the OR relationship, indicating that the condition is met if any keyword not preceded by an operator is included in the search result.
Pay attention to the following points when you use the BOOLEAN full-text search mode:
Operators must precede keywords. The operator following a keyword is not taken as a modifier. For example, the plus sign (+) in "+chrysanthemum" is a modifier, while that in "chrysanthemum+" is taken as part of a keyword.
Operators and keywords must be closely connected, and cannot be separated by other signs. For example, the plus sign (+) in "+ chrysanthemum" is not taken as a modifier.