SQLite3 storing String which contains only numbers.

Question

I have to run a very simple query like this in SQLite3.

INSERT OR REPLACE INTO tblPhoneList(Phone, PeopleId, StorageType) VALUES('+91912345789', '1','1');

But whatever I do, the number is stored as 91912345789. The + prefix is ignored. The field Phone is a String. Why does it ignore the + prefix? Is there anyway to avoid this?

P.S. This code is run inside Android

EDIT: This is the schema of the table

CREATE TABLE tblPhoneList(Phone STRING PRIMARY KEY ON CONFLICT REPLACE, StorageT
ype INTEGER, PeopleId INTEGER, FOREIGN KEY(PeopleId) references tblPeople(id));

Answer 1

the field Phone in your DB cannot be a String, you'd better check it, TEXT or VARCHAR may work. Good Luck!

Answer 2

It's text! SQLite only supports Integer, Text, Real and BLOB.

Answer 3

<UPDATE> from the manual:

the declared type of "STRING" has an affinity of NUMERIC, not TEXT

so please change the type of this column to TEXT, and you should be done. </UPDATE END>

what is the type of your Phone column (try PRAGMA table_info (tblPhoneList))?. if it is TEXT (or BLOB), you shouldn't have any problems - just tried this here. if it is INTEGER or REAL, you should convert it to TEXT - you cannot expect an INTEGER or REAL column to store the + string.

for all the details, see Datatypes In SQLite Version 3.

Answer 4

try this:

INSERT OR REPLACE INTO tblPhoneList(Phone, PeopleId, StorageType) VALUES('\+91912345789', '1','1');

Answer 5

Thanks for all the replies. I changed the type String (yes, String type IS supported) to Varchar(32) and this error magically goes off. Looks like String type is poorly supported in here.