Menu
I ran sqlite3 on my command prompt and ran some basic SQL commands.
user@comp:~$ sqlite3
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE A (a int, b text, c float);
sqlite> INSERT INTO A(a,b,c) VALUES (1, '2', 3);
sqlite> SELECT b::int+2 FROM A;
All of the lines work except for the last one, which gives the error: `
Error: unrecognized token: ":"`
I was reading from this question (Double colon (::) notation in SQL) that the double colon notation is a type-cast for SQL. Am I doing the operation wrong?
662
SQLite CAST operator:The CAST operator is used to convert a value from a data type to another data type. For example, if you have a numeric value stored as a string value like this ” '12.5' ” and you want to convert it to be a numeric value you can use the CAST operator to do this like this “CAST( '12.5' AS REAL)“.
To convert the results of an SQL expression to a boolean value, SQLite first casts the result to a NUMERIC value in the same way as a CAST expression.
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.0 (2018-04-02) but those keywords are really just alternative spellings for the integer literals 1 and 0 respectively.
The :: syntax is PostgreSQL specific. You could use ANSI standard instead:
SELECT CAST(b AS INT) + 2 AS alias
FROM A
SqlFiddleDemo
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
