Menu
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?
228
If the declared column type has the string “INT” then it's allotted integer affinity. If the declared column type has any of the strings like “TEXT”, “VARCHAR” or “CLOB” then that column has TEXT affinity. If the declared column type has the string “BLOB” or if no kind is given then the column has BLOB affinity.
You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
SQLite only has four primitive data types: INTEGER, REAL, TEXT, and BLOB. APIs that return database values as an object will only ever return one of these four types.
Use:
PRAGMA table_info(table-name);
to get table info.
176
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).
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
