July 14, 2011

sqlite 3 string to integer conversion, similar to C function atoi

In sqlite3, if you have a column defined as string, but you want to use it as integer when doing comparison, you can do the following to convert it.

CAST(expr AS type)

For example: you have a column named "version", and you stored value "11" in it. Now you can convert that to integer by doing:

Select * from mytable where CAST(version as integer)>=11;

Hope this helps you.

13 comments:

  1. thank you!

    ReplyDelete
  2. thanks alot

    ReplyDelete
  3. Note that for integers, you can also do an implicit cast: "SELECT numstring + 0 FROM ...".

    ReplyDelete
  4. ¡Muchas gracias!, es justo lo que necesitaba.

    ReplyDelete
  5. thanks!!!!!!!!!

    ReplyDelete
  6. it did help me, thanks :)

    ReplyDelete
  7. how to convert hex value to string in sqlite.

    ReplyDelete
  8. Thank you very much!

    ReplyDelete
  9. Thanks very much!! It's very useful.
    In fact, it also can be used at the ORDER BY clause:

    ORDER BY CAST("age" as integer)

    and also exist the conversion to real numeric (=float):

    ORDER BY CAST("price" as real)

    ReplyDelete