Functions
Supported 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:
|
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') |
- |
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
Item |
Restriction |
---|---|
ROW_COUNT() |
Function ROW_COUNT() is not supported. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.