0

I'm writing on a little c# tool that will change columns of databases. It should also multiply a column by a certain factor according to user input. Of course it works with

UPDATE table SET column = column * factor

however this doesn't consider the data type of the column, so in case of a wrong user input a column with data type "TEXT" will also be multiplied without any error message resulting in zeros.

I'm aware I could get the type of the column into my c# program and test for it before updating, however in my case this would be slow and impractical since I'm working only with the information from the sql's I get from 

SELECT * FROM sqlite_master

Is there a way to the needed feedback directly within sqlite?

Roland Deschain
  • 2,211
  • 19
  • 50
  • SQLite *does* take the data type into account as explaine in [Data Types - Operators](https://sqlite.org/datatype3.html#operators). `An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.` – Panagiotis Kanavos Jul 04 '17 at 11:31
  • You shoudl use `typeof()` to filter out non-numeric values as [shown in this question](https://stackoverflow.com/questions/32528759/how-to-check-if-a-value-is-a-number-in-sqlite), eg `where typeof(column)= 'integer'` – Panagiotis Kanavos Jul 04 '17 at 11:35

1 Answers1

0

You shoudl use typeof() to filter out non-numeric values as shown in this question, eg where typeof(column)= 'integer'

This answer by Panagiotis Kanavos is correct, I actually tried that before but I used capitals (INTEGER vs. integer) for the types which didn't work.

Thx for the answer!

Roland Deschain
  • 2,211
  • 19
  • 50