CODEWITHSUNDEEP
sqlite
Michael Stoner
Michael Stoner

Reputation: 1401

Difference Between "Text" and "String" datatype in SQLite

I have a SQLite database with my Android application. I have noticed I accidentally defined a table using a "String" datatype instead of "Text" datatype. Here is the code with string:

private final String LISTDATES_CREATE = "create table if not exists ListDates (_id integer primary key autoincrement, ListName string not null, UpdateDate string not null);";

This works. It has never thrown an error and I can store and retrieve data. However I can't find any reference to a "String" datatype in SQLite in the documentation or on the internet. Typically, all string type data is defined with "text" like so:

private final String LISTDATES_CREATE = "create table if not exists ListDates (_id integer primary key autoincrement, ListName text not null, UpdateDate text not null);";

So my question is, what is the difference between a field defined with a "string" datatype versus a "text" datatype? Is there a difference? If so, what are the consequences, if any, of using one or the other?

Upvotes: 42

Views: 48741

Answers (2)

Phillip Ngan
Phillip Ngan

Reputation: 16126

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.

Upvotes: 53

wsanville
wsanville

Reputation: 37516

The subtle thing to note here is that SQLite does not enforce the data type of values you put into columns. That means that you can put text into a numeric field, and so on.

To understand the difference between your two SQL statements, check out section 2.1 Determination Of Column Affinity, which maps the column types you provide to the storage classes SQLite uses.

In this case, the type string gets mapped to storage class NUMERIC via rule 5. Declaring the field as text in code would tell the DBMS to use the TEXT storage class. Again, since SQLite does not enforce the types of columns, your code will probably run fine when storing Strings as a NUMERIC column, as you note.

As an alternative example, you could define a column with type INTERESTING STUFF, and that would be mapped to the INTEGER storage class, via rule 1.

Overall, it's probably a good idea to just use text for your table definition.

Upvotes: 45

Related Questions