Determine data type of a column in SQLite

I'm working on an Android App where the user has different options for sorting the displayed data that comes from the database. Currently my orderBy string that I pass to Androids query() method looks like this:

"LOWER("+columnName+") ASC"

The problem with this is that if the data type in the column specified by columnName is integer, calling LOWER() on it will cause it to be sorted alphabetically, i.e. based only on the leftmost digit, which of course doesn't make any sense for numeric data. Hence I only want to apply LOWER() if the data type of the column is not integer. What I have in mind is a statement like this:

"CASE WHEN [data type of columnName is integer] THEN "+columnName+" ASC ELSE LOWER("+columName+开发者_JS百科") ASC END"

The part in the brackets is what I don't know how to do. Does SQLite provide a function to determine a column's data type?

Do you really want the type of the column, or the type of the value? (SQLite is dynamically-typed, so the distinction is important.)

If you want the latter, you can use typeof(columnName).

Use:

PRAGMA table_info(table-name);

to get table info.

Taken directly from SQLite docs about datatypes for SQLite Version 3:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

Column affinity: use PRAGMA table_info(table-name);. PRAGMA table_info() gives a table with columns cid, name, type, notnull, dflt_value, and pk.

Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Datatype of value: Use typeof(column) to see how values are actually stored by SQLite.

Example adapted from section 3.4:

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);

-- BLOBs are always stored as BLOBs regardless of column affinity.
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');

-- NULLs are also unaffected by affinity
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

Output of PRAGMA table_info(t1);:

0|t|TEXT|0||0
1|nu|NUMERIC|0||0
2|i|INTEGER|0||0
3|r|REAL|0||0
4|no|BLOB|0||0

Output of SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; (notice each value in a column has its own datatype):

text|integer|integer|real|text
text|integer|integer|real|real
text|integer|integer|real|integer
blob|blob|blob|blob|blob
null|null|null|null|null

Did you declare the column as an integer when setting up the table? Otherwise sqlite will store it as text and the sorts will act as you've described.

create table if not exists exampletable (columnName integer);

To get information of Table use

PRAGMA table_info(table-name);

If you want the latter, you can use

typeof(columnName)