Difference Between “Text” and “String” datatype in SQLite

This is an old question, but I want to highlight a specific difference between a STRING and TEXT column types. If a string looks like a numeric value, STRING will convert it into a numeric value, while TEXT will not perform any conversion.

e.g.

create table t1(myint INTEGER, mystring STRING, mytext TEXT);
insert into t1 values ('0110', '0220', '0330');
insert into t1 values ('-0110', '-0220', '-0330');
insert into t1 values ('+0110', '+0220', '+0330');
insert into t1 values ('x0110', 'x0220', 'x0330');
insert into t1 values ('011.0', '022.0', '033.0');
select * from t1

will output rows with values:

 myint mystring mytext
   110      220   0330
  -110     -220  -0330
   110      220  +0330
 x0110    x0220  x0330
    11       22  033.0

This means leading zeros, zeros trailing decimal points, and plus symbol on the “numeric” values will be stripped. This will cause problems if you intend on doing a string match using the values read out of the table.