Is it possible to filter SQLite column values in SQL based on whether the value is numeric or textual? I have seen references to using CAST for this purpose. However, at appears to be useless as SELECT CAST('1a' AS NUMERIC) passes the check for a numeric type.

CodePudding user response：

Thats just an idea: SELECT [FilterColumn] FROM [Table] WHERE [FilterColumn]='0' OR (ceiling(log([FilterColumn],10)) =LENGTH([FilterColumn]) AND CAST([FilterColumn] AS INTEGER)>0)

This works for integer numbers where number of digits=log([FilterColumn],10). To distinguish a single letter from casting to 0, [FilterColumn]='0' OR [FilterColumn]>0 included.

I suppose there are more elegant solutions

CodePudding user response：

The typeof() SQL function is designated for type checking. However, its result depends on both column type definition (according to the official docs) and the format used during insertion. For example, when a number is inserted as a text literal into a NUMERIC column, it is converted into a number if possible, and typeof() will return an appropriate numeric type or text, if conversion did not occur. The TEXT column, on the other hand, stores all numeric literals as text. BLOB column stores textual and numeric literals without interpretation. Therefore, a mixed-type column should be probably declared as BLOB or NUMERIC (depending on whether textual literals needs to be converted to numbers, if possible). With this behavior in mind, typeof() is well suitable for type checking.