SQLite: String automatically converted to integer in string column

Question

I am trying to insert a string "02" into a string column. I can very clearly see that "02" is being issued as the command "REPLACE INTO Users (ID, Terminal) values ('00000000001', '02')". However when it is placed in the database it is entered as seemingly an integer (removing the leading zero) and then causes an error when reading it in as a string.

The extremely confusing part is that the ID string works (perhaps because the column is (ID VARCHAR(11) NOT NULL) while Terminal is a string? I know that if the column were set to integer this behaviour would make sense but I have set the column to a string and I can see in the schema it is one. I feel like I must be missing something obvious but I can't spot it.

String is converted to integer in string column while I am definitely entering it as a string and am definitely entering it into a string column. The only way I can seemingly get this to work is to add an alpha character into the string and it will be inserted properly which I'll have to remove when retrieving but that seems like a poor solution just to get the type correct.

sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html — Jon Clements
– Jon Clements, Commented Nov 19, 2018 at 20:35
Thank you very much, since I didn't get an error declaring the column "string" I assumed it was doing what I wanted, but changing "string" to "text" has fixed my issue. I knew it was something silly. — Ryan D.
– Ryan D., Commented Nov 19, 2018 at 21:13

MikeT · Accepted Answer · 2018-11-19 22:46:09Z

string is not a known type affinity and thus it is converted according to the rules (see paragraph 3.1 in the link below)

It does not contain INT (so is not given a type affinity of INTEGER).
It does not contain CHAR, CLOB or TEXT (so is not given a type affinity of TEXT (VARCHAR(11) is given a type afinity of TEXT)).
It does not contain BLOB (so is not given a type affinity of BLOB)
It does not contain REAL, FLOA or DOUB (so is not given a type affinity of REAL).
IT THEREFORE is given a type affinity of NUMERIC and thus :-

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

Datatypes In SQLite Version 3

So you need to change the type affinity to meet the 2nd rule.

e.g. consider the following :-

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS users_alt;
CREATE TABLE IF NOT EXISTS users(id VARCHAR(11), terminal string);
CREATE TABLE IF NOT EXISTS users_alt(id VARCHAR(11), terminal TEXT);
INSERT INTO users VALUES('00000000001','01');
INSERT INTO users_alt VALUES('00000000001','01');
REPLACE INTO users (id,terminal) VALUES('00000000001','02');
REPLACE INTO users_alt (id,terminal) VALUES('00000000001','02');
SELECT * FROM users;
SELECT * FROM users_alt;

The 1st result, for the users table is :-

Whilst the 2nd result, for the users_alt table is :-

Alternately you could do something like :-

SELECT id,
    CASE 
        WHEN length(terminal) = 2 THEN terminal
        ELSE '0'||terminal
    END AS terminal
FROM users;

Which would result in :-

NOTE obviously the above has limitations and is just form demonstration.

Collectives™ on Stack Overflow

SQLite: String automatically converted to integer in string column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related