Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to type cast in sqlite3

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?

like image 662
Abundance Avatar asked Oct 05 '15 16:10

Abundance


People also ask

How do I cast from SQLite?

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)“.

Does SQLite support cast?

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.

Does SQLite have Boolean?

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.


1 Answers

The :: syntax is PostgreSQL specific. You could use ANSI standard instead:

SELECT CAST(b AS INT) + 2 AS alias
FROM A

SqlFiddleDemo

like image 170
Lukasz Szozda Avatar answered Sep 18 '22 09:09

Lukasz Szozda