Re: [sqlite] CAST AS STRING always returns 0 for STRING columns

[Keith Medcalf]

"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-
>boun...@mailinglists.sqlite.org] On Behalf Of Ben Caine
>Sent: Tuesday, 4 September, 2018 08:31
>To: sqlite-users@mailinglists.sqlite.org
>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
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CAST AS STRING always returns 0 for STRING columns

[Jay Kreibich]

> On Sep 4, 2018, at 9:30 AM, Ben Caine  wrote:
> 
> CAST AS STRING always returns 0 for columns that are already of STRING type.

“STRING” is not a known type affinity.  Use “TEXT”

https://www.sqlite.org/lang_expr.html#castexpr 




> 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CAST AS STRING always returns 0 for STRING columns

[Ben Caine]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users