Converting text to int in sqlite when querying

Question

I want to convert both of the following columns to integer (they were placed as text in the SQlite db) as soon as I select them.

 string sql4 = "select seq, maxLen from abc where maxLen > 30";

I think it might be done like this..(using cast)

 string sql4 = "select cast( seq as int, maxLen as int) from abc where maxLen > 30";

Not sure if it's right as I seem to be getting a syntax error.

How would I also convert the text to double

Answer 1

You need to cast in where clause, not where you are selecting it.

string sql4 = "select seq, maxLen from abc where CAST(maxLen as INTEGER) > 30";

Also in your current cast version it will not work since CAST works for a single field.

For your question:

How would I also convert the text to double

cast it to REAL like:

CAST(maxLen as REAL)

Answer 2

Syntax issue is you're putting two casts in one cast clause. Try this:

string sql4 = "select cast(seq as int), cast(maxLen as int) 
               from abc where maxLen > 30"

Like Habib pointed out, you should typecast the where clause, otherwise the comparison isn't numeric but textual.

string sql4 = "select cast(seq as int), cast(maxLen as int) 
               from abc where cast(maxLen as int) > 30"

And also, casting to float is simple use float instead of int (or you can use REAL which is same datatype in SQLite)

cast(maxLen as float)