Functions and conditions
String functions: LOWER(string) UPPER(string) CONCAT(string, string) DATE() LENGTH(string) MAX(String) MIN(String) TRIM(X,Y) - trims Y from both ends of string X. INSTR(String X, String Y) - finds the first occurrence of Y in string X (see documentation) SUBSTR(X,Y,Z) - returns a substring from X starting at Y, with length Z REPLACE(X,Y,Z) - in string X replaces Y with Z CAST(something AS TEXT|INTEGER|REAL) - converts from one data type to another IFNULL(first parameter, second parameter) - equals the first parameter, but if the first is null then the second. SELECT FAX, IFNULL(FAX, 'Call phone: '||Phone) FROM customers; COALESCE(param1,param2......) - returns the first NOT null parameter
Numeric functions: ROUND(X,Y) - Rounds number X to Y decimal places.
IF ELSE in SQLite:
SELECT
CASE ReportsTo
WHEN 1 THEN 'ok'
WHEN 2 THEN 'not ok'
ELSE 'Undef(null)'
END as "Okie-Dokie"
FROM employees;
Syntax:
or