"STRING" is not a known affinity and equates to NUMERIC affinity/storage class.
That means that your code is the equivalent of:
create table a(col1 NUMERIC);
insert into a values ('asdf');
select cast(col1 as NUMERIC) from a;
Which will, of course, return the value 0. This is because:
1) You create the table a with the col1 column having an affinity of NUMERIC.
2) You attempted to insert TEXT into that column.
The TEXT you provided could not be losslessly converted to the affinity of the
column, so it was stored as TEXT. (That means that when the text value was
converted to a numeric value, that value could not then be converted back to
the original input string).
3) When you select the data you are converting the TEXT into NUMERIC without
regard for the "losslessly" part. It is assumed that you know what you are
doing. The result of the conversion is 0, which is correct.
The valid datatypes (affinities and storage classes) are:
INTEGER/REAL/TEXT/BLOB/NUMERIC. When you declare a "datatype" or "affinity"
using a sequence of characters ("STRING" being the sequence of characters you
chose), the rules outlined here are used to determine the actual
datatype/storage class/affinity: https://www.sqlite.org/datatype3.html#affname
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[email protected]] On Behalf Of Ben Caine
>Sent: Tuesday, 4 September, 2018 08:31
>To: [email protected]
>Subject: [sqlite] CAST AS STRING always returns 0 for STRING columns
>
>CAST AS STRING always returns 0 for columns that are already of
>STRING type.
>
>Steps to reproduce:
>
>sqlite> CREATE TABLE A(col1 STRING);
>sqlite> INSERT INTO A VALUES('asdf');
>sqlite> SELECT CAST(col1 AS STRING) FROM A;
>0
>
>We are generating SQL code programmatically, and it would be useful
>to not
>have to special-case casting based on column type.
>--
>--
>
>Ben Caine | Software Engineer | Verily
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
