Hi Peter, From https://www.sqlite.org/datatype3.html:
"When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible"; basically any fraction you insert into a NUMERIC column with at most 15 digits will be converted do REAL (float). What you are looking for is DECIMAL data type, which is not supported by sqlite, and NUMERIC is not supposed to be a replacement, NUMERIC is only useful as an extension beyond the range of INTEGER or FLOAT for huge numbers or extreme precision, in which case it will behave as TEXT and it's the responsibility of the client app to convert back to some numeric representation. Do note however, that if you're dealing with only positive fractions, you can safely use TEXT to emulate DECIMAL, as long as all strings are the same length and right-aligned: " 100.50" < "999.00" On 12.03.2015 01:27, Peter Haworth wrote: > I have a table, Transactions, with a column , Value, of type NUMERIC. The > Value column is supposed to have 2 decimal places in all rows but some have > only one. > > To correct this I issued the following command: > > UPDATE Transactions SET Value=Value||0 WHERE substr(Value,-2,1)='.' > > No errors on execution but nothing was changed. > > To help me figure out why that didn't work, I issued the following command: > > SELECT Value||0 FROM Transactions WHERE substr(Value,-2,1)='.' > > This time, all the values with only 1 decimal place were listed with a > trailing zero added. > > On the face of it, the WHERE statement works fine in a SELECT statement but > does not find any rows in an UPDATE statement. Either that or the SET is > not calculating the correct value. > > Is this a bug or am I missing something? > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
