Determine data type of a column in SQLite

Question

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+") 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?

Answer 1

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);

Answer 2

Use:

PRAGMA table_info(table-name);

to get table info.

Answer 3

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).