Return to Misconfigured,
A. Operators and Functions B. Supplementary Topics
Appendix A. Operators and Functions This appendix covers common operations and functions used in SQLite as a convenient reference. While this book does not cover all the functionalities of SQLite, the functionalities likely to have an immediate need can be found here. The primary goal is to showcase universal SQL concepts that apply to most platforms, not to teach the nuances of the SQLite platform in detail.
A comprehensive coverage of SQLite’s features can be found at https://www.sqlite.org/docs.html.
Appendix A1 – Literal Expression Queries You can test operators and functions easily without querying any tables at all. You simply SELECT an expression of literals as in the following query, which will calculate a single value of 12:
SELECT 5 + 7 Any functions and literals, including text strings, can be tested in this manner as well. This query will check if the word 'TONY' is in the string 'TONY STARK', and it should return 1:
SELECT INSTR('TONY STARK', 'TONY') This is a great way to test operators and functions without using any tables. This appendix will show many examples with this approach, and you can use it for your own experimentation.
Appendix A2 – Mathematical Operators SQLite has a small set of basic math operators. More advanced tasks are usually done with functions, but here are the five core mathematical operators.
Assume x = 7 and y = 3
Operator Description Example Result + Adds two numbers x + y 10 - Subtracts two numbers x - y 4
- Multiplies two numbers x * y 21
/ Divides two numbers x / y 2 % Divides two numbers, but returns the remainder x % y 1 Appendix A3 – Comparison Operators Comparison operators yield a true (1) or false (0) value based on a comparative evaluation.
Assume x = 5 and y = 10
Operator Description Example Result = and == Checks if two values are equal x = y 0 (false) != and <> Checks if two values are not equal x != y 1 (true)
Checks if value on left is greater than value on right x > y 0 (false)
< Checks if value on left is less than value on right x < y 1 (true)
= Checks if value on left is greater than or equal to value on right x >= y 0 (false)
⇐ Checks if value on left is less than or equal to value on right x ⇐ y 1 (true) APPENDIX A4 – Logical Operators Logical operators allow you combine Boolean expressions as well as perform more conditional operations.
Assume x = true (1) and y = false (0)
Assume a = 4 and b = 10
Operator Description Example Result AND Checks for all Boolean expressions to be true x AND y 0 (false) OR Checks for any Boolean expression to be true x OR y 1 (true) BETWEEN Checks if a value inclusively falls inside a range a BETWEEN 1 and b 1 (true) IN Checks if a value is in a list of values a IN (1,5,6,7) 0 (false) NOT Negates and flips a Boolean expression’s value a NOT IN (1,5,6,7) 1 (true) IS NULL Checks if a value is null a IS NULL 0 (false) IS NOT NULL Checks if a value is not null a IS NOT NULL 1 (true) APPENDIX A5 – Text Operators Text has a limited set of operators, as most text processing tasks are done with functions. There are a few, though. Keep in mind also that regular expressions are beyond the scope of this book, but they are worth studying if you ever work heavily with text patterns.
Assume city = ‘Dallas’ and state = ‘TX’
Operator Description Example Result ]] | sql_operators_and_functions | Concatenates one or more values together into text city | sql_operators_and_functions | ', ' | sql_operators_and_functions | //www.sqlite.org/lang_datefunc.html to get a comprehensive list of these functionalities. ==Fair Use Sources== [[Fair Use Source: