Sqlite spec: 4.2. Type Conversions Prior To Comparison

by

The question:

Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:

If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand.

I would expect the following query:

CREATE TABLE t (id integer primary key, str varchar(20));
INSERT INTO t (id, str) VALUES (1, '5'), (2, '5u');

SELECT id, 5 = str, 5 >= str, CAST(5 AS NUMERIC) >= str, CAST(str AS NUMERIC) FROM t;

to return 5 >= str = 1 for both rows as the left side operand has NUMERIC affinity.

Demo: http://sqlfiddle.com/#!5/e9c19/4

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

I think you may be missing this part:

with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a value if the change is lossless and reversible

(emphasis mine).


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment