Mixing
SQLite: String automatically converted to integer in string column
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 replace
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
add a comment |
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 replace
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 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.
Nov 19 '18 at 21:13
add a comment |
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 replace
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
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 replace
sqlite replace
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
asked Nov 19 '18 at 20:31
Ryan D.Ryan D.
253
253
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 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.
Nov 19 '18 at 21:13
add a comment |
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 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.
Nov 19 '18 at 21:13
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 at 20:35
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 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.
Nov 19 '18 at 21:13
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.
Nov 19 '18 at 21:13
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382202%2fsqlite-string-automatically-converted-to-integer-in-string-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
add a comment |
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.
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
add a comment |
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.
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
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.
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
edited Nov 19 '18 at 22:46
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
answered Nov 19 '18 at 22:31
MikeTMikeT
15.3k112441
15.3k112441
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53382202%2fsqlite-string-automatically-converted-to-integer-in-string-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
sqlite is deliberately not completely strict on typing - you might want to read through sqlite.org/datatype3.html
– Jon Clements♦
Nov 19 '18 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.
Nov 19 '18 at 21:13