Updated on 2022-02-22 GMT+08:00

Functions

Supported Functions

Table 1 Operator functions

Expression

Example

IN

SELECT * FROM Products WHERE vendor_id IN ( 'V000001', 'V000010' ) ORDER BY product_price

NOT IN

SELECT product_id, product_name FROM Products WHERE NOT vendor_id IN ('V000001', 'V000002') ORDER BY product_id

BETWEEN

SELECT id, product_id, product_name, product_price FROM Products WHERE id BETWEEN 000005 AND 000034 ORDER BY id

NOT...BETWEEN

SELECT product_id, product_name FROM Products WHERE NOT vendor_id BETWEEN 'V000002' and 'V000005' ORDER BY product_id

IS NULL

SELECT product_name FROM Products WHERE product_price IS NULL

IS NOT NULL

SELECT id, product_name FROM Products WHERE product_price IS NOT NULL ORDER BY id

AND

SELECT * FROM Products WHERE vendor_id = 'V000001' AND product_price <= 4000 ORDER BY product_price

OR

SELECT * FROM Products WHERE vendor_id = 'V000001' OR vendor_id = 'V000009'

NOT

SELECT product_id, product_name FROM Products WHERE NOT vendor_id = 'V000002'

LIKE

SELECT * FROM Products WHERE product_name LIKE 'NAME%' ORDER BY product_name

NOT LIKE

SELECT * FROM Products WHERE product_name NOT LIKE 'NAME%' ORDER BY product_name

CONCAT

SELECT product_id, product_name, Concat( product_id , '(', product_name ,')' ) AS product_test FROM Products ORDER BY product_id

+

SELECT 3 * 2+5-100/50

-

SELECT 3 * 2+5-100/50

*

SELECT order_num, product_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num BETWEEN 000009 AND 000028 ORDER BY order_num

/

SELECT 3 * 2+5-100/50

UPPER

SELECT id, product_id, UPPER(product_name) FROM Products WHERE id > 10 ORDER BY product_id

LOWER

SELECT id, product_id, LOWER(product_name) FROM Products WHERE id <= 10 ORDER BY product_id

SOUNDEX

SELECT * FROM Vendors WHERE SOUNDEX(vendor_name) = SOUNDEX('Huawee') ORDER BY vendor_name

IFNULL

SELECT IFNULL(product_id, 0) FROM Products;

NOTE:
  • For DDM instances created before March 20, sharded tables do not support the calling of functions nested in the IFNULL and aggregation functions. For example, if you execute function select IFNULL(sum(yan),0) from shenhai, the result differs from the expected result.
  • For DDM instances created after March 20, sharded tables support only the calling of functions nested in the IFNULL and aggregation functions.
Table 2 Time and date functions

Function Expression

Example

DAY()

SELECT * FROM TAB_DT WHERE DAY(dt)=21

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

MONTH()

SELECT * FROM TAB_DT WHERE MONTH(dt)=12

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

YEAR()

SELECT * FROM TAB_DT WHERE YEAR(dt)=2018

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

DAYOFYEAR()

SELECT * FROM TAB_DT WHERE DAYOFYEAR(dt)=365

SELECT * FROM TAB_DT WHERE dt='2018-12-31'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

DAYOFWEEK()

SELECT * FROM TAB_DT WHERE DAYOFWEEK(dt)=6

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

WEEKOFYEAR()

SELECT * FROM TAB_DT WHERE WEEKOFYEAR(dt)=51

SELECT * FROM TAB_DT WHERE dt='2018-12-21'

INSERT INTO TAB_DT(id,dt) VALUES(1,'2018-05-22')

Table 3 Mathematical functions

Expression

Example

SQRT()

SELECT id, product_price, SQRT(product_price) AS price_sqrt FROM Products WHERE product_price < 4000 ORDER BY product_price

AVG()

SELECT AVG(product_price) AS avg_product FROM Products

COUNT()

SELECT COUNT(*) AS num_product FROM Products

MAX()

SELECT id, product_id, product_name, MAX(product_price) AS max_price FROM Products ORDER BY id

MIN()

SELECT id, product_id, product_name, MIN(product_price) AS min_price FROM Products ORDER BY id

SUM()

SELECT SUM(product_price) AS sum_product FROM Products

Unsupported Functions

Table 4 Function restrictions

Item

Restriction

Functions

Function row_count() is not supported.