Help Center/ Distributed Database Middleware/ User Guide/ SQL Syntax/ Functions
Updated on 2024-04-01 GMT+08:00
View PDF
Share

Functions

Supported Functions

Table 1 Operator functions

Function 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;
Table 2 Time and date functions

Function 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');

-

TIME()

SELECT * FROM TAB_DATE WHERE TIME(date)='01:02:03';

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid time or datetime expression or character string.

TIME_TO_SEC()

SELECT * FROM TAB_DATE WHERE TIME_TO_SEC(date)=3603;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:00:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:00:03');

The parameter must be a valid time or datetime expression or character string.

SEC_TO_TIME()

SELECT * FROM TAB_DATE WHERE SEC_TO_TIME(date)='00:01:00';

SELECT * FROM TAB_DATE WHERE date=60;

INSERT INTO TAB_DATE(id,date) VALUES(1,60);

The parameter must be a numerical value or a character string that can be converted into a numerical value.

SECOND()

SELECT * FROM TAB_DATE WHERE SECOND(date)=3;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid time or datetime expression or character string.

MINUTE()

SELECT * FROM TAB_DATE WHERE MINUTE(date)=2;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid time or datetime expression or character string.

HOUR()

SELECT * FROM TAB_DATE WHERE HOUR(date)=1;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid time or datetime expression or character string.

DAYNAME()

SELECT * FROM TAB_DATE WHERE DAYNAME(date)='Friday';

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid date or datetime expression or character string.

MONTHNAME()

SELECT * FROM TAB_DATE WHERE MONTHNAME(date)='January';

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid date or datetime expression or character string.

LAST_DAY()

SELECT * FROM TAB_DATE WHERE LAST_DAY(date)='2021-01-31';

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid date or datetime expression or character string.

DAYOFWEEK()

SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid date or datetime expression or character string.

DAYOFMONTH()

SELECT * FROM TAB_DATE WHERE DAYOFWEEK(date)=6;

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');

The parameter must be a valid date or datetime expression or character string.

DAYOFYEAR()

SELECT * FROM TAB_DATE WHERE DAYOFYEAR(date)=365;

SELECT * FROM TAB_DATE WHERE date='2021-12-31 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03');

The parameter must be a valid date or datetime expression or character string.

WEEKOFYEAR()

SELECT * FROM TAB_DATE WHERE WEEKOFYEAR(date)=53;

SELECT * FROM TAB_DATE WHERE date='2021-12-31 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-12-31 01:02:03');

The parameter must be a valid date or datetime expression or character string.

DATE_ADD(

date, INTERVAL expr unit

)

SELECT * FROM TAB_DATE WHERE DATE_ADD(date,INTERVAL 1 YEAR)='2022-01-01 01:02:03';

SELECT * FROM TAB_DATE WHERE date='2021-01-01 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-01 01:02:03');
  • Parameter date must be a valid time, date or datetime expression or character string.
  • expr indicates the date interval. This parameter must be an integer or a character string that can be converted into an integer.
  • unit indicates the time unit. The value can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
  • If date indicates a date, its lower boundary value is 1000-01-01. If the value is out of the range, an error may be reported.
  • The date parameter can be precise to milliseconds.

DATE_SUB(date, INTERVAL expr unit)

SELECT * FROM TAB_DATE WHERE DATE_SUB(date,INTERVAL -1 DAY)='2021-01-02 01:02:03';

SELECT * FROM TAB_DATE WHERE date='2021-01-02 01:02:03';

INSERT INTO TAB_DATE(id,date) VALUES(1,'2021-01-02 01:02:03');
  • Parameter date must be a valid time, date or datetime expression or character string.
  • expr indicates the date interval. This parameter must be an integer or a character string that can be converted into an integer.
  • unit indicates the time unit. The value can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
  • If date indicates a date, its lower boundary value is 1000-01-01. If the value is out of the range, an error may be reported.
  • The date parameter can be precise to milliseconds.
Table 3 Mathematical functions

Function 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;

-

ROUND()

SELECT ROUND(product_price) AS round_product FROM Products;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

SIN()

SELECT SIN(x) AS sin_x FROM math_tbl;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

COS()

SELECT COS(x) AS cos_x FROM math_tbl;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

TAN()

SELECT TAN(x) AS tan_x FROM math_tbl;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

COT()

SELECT COT(x) AS cot_x FROM math_tbl;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

FLOOR()

SELECT FLOOR(product_price) AS floor_product FROM Products;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

CEILING()

SELECT CEILING(product_price) AS ceiling_product FROM Products;

The parameter must be a numerical value or a character string that can be converted into a numerical value.

ABS()

SELECT ABS(x) AS abs_x FROM math_tbl;

The value ranges from -9223372036854775807 to 9223372036854775807. If the value is out of the range, an error is reported.

LOG()

SELECT LOG(x) AS log_x FROM math_tbl;

The parameter must be a numerical value greater than 0 or a character string that can be converted into a numerical value.

LN()

SELECT LN(x) AS ln_x FROM math_tbl;

The parameter must be a numerical value greater than 0 or a character string that can be converted into a numerical value.

EXP()

SELECT EXP(x) AS exp_x FROM math_tbl;

The value ranges from -∞ to 709. If the value is out of the range, an error is reported.
Table 4 Character string functions

Function Expression

Example

Application Scope

TRIM()

SELECT TRIM(' hello, world ') AS trim_character FROM Character;

The parameter must be a character string or of a similar data type.

You are advised to use a supported function. Before using a function, check whether it is supported. If you use an unsupported function, the returned result may be different from that in MySQL.

Unsupported Functions

Table 5 Function restrictions

Item

Restriction

ROW_COUNT()

Function ROW_COUNT() is not supported.

COMPRESS()

Function COMPRESS() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

SHA()

Function SHA() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

SHA1()

Function SHA1() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

MD5()

Function MD5() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

AES_ENCRYPT()

Function AES_ENCRYPT() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

AES_DECRYPT()

Function AES_DECRYPT() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

YEARWEEK()

Function YEARWEEK() is not supported. If you are not sure whether the function can be pushed down to RDS, do not use it.

TIME_FORMAT()

Function TIME_FORMAT() is not supported. If you are not sure whether the function can be pushed down to RDS, use function DATE_FORMAT().
Parent topic: SQL Syntax