On 2017/07/11 2:31 PM, Richard Hipp wrote:
Truncation is a string operation, not a mathematical operation. So I suggest using string functions:WITH SRC(Val) AS ( VALUES (0),(1.0000001),(1.12345678),(1.99999999),(1.888), (9.87654321),(1.555555555),(1.4999999),(1.49494999), (12345.67890123), (1234.56) UNION ALL SELECT -Val FROM SRC LIMIT 18 ) SELECT substr(val,1,instr(val,'.')+3) FROM src;
Yep, that's a good method, exactly what Simon suggested, and as with his suggestion, if we simply add a CAST to REAL we end up with a numeric value that can be used in further computations. I opted for a length() on a printf statement (when implementing Simon's suggestion) to determine the truncation point because I'm not exactly sure if the decimal separator will always be a "." inside SQLite regardless of any localization setting - but if it is, this method is certainly shorter.
I still think Keith's method will win for speed, if string functions are slower than math functions, but I could be wrong. I suppose a speedtest is in order, but I have meetings, perhaps someone else fancies some testing.
_______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
