Updated on 2022-09-16 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('test') 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

Expression

Example

Application Scope

DAY()

SELECT * FROM TAB_DATE WHERE DAY(date)=21

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

MONTH()

SELECT * FROM TAB_DATE WHERE MONTH(date)=12

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

YEAR()

SELECT * FROM TAB_DATE WHERE YEAR(date)=2018

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

DAYOFYEAR()

SELECT * FROM TAB_DATE WHERE DAYOFYEAR(date)=365

SELECT * FROM TAB_DATE WHERE date='2018-12-31'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

DAYOFWEEK()

SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

WEEKOFYEAR()

SELECT * FROM TAB_DATE WHERE WEEKOFYEAR(date)=51

SELECT * FROM TAB_DATE WHERE date='2018-12-21'

INSERT INTO TAB_DATE(id,date) VALUES(1,'2018-05-22')

-

Table 3 Mathematical functions

Expression

Example

Application Scope

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

ROW_COUNT()

Function ROW_COUNT() is not supported.