QGIS convert long integers stored as string to integer [duplicate]

Question

I have long integers (11 characters) stored as text in a shapefile that I need to convert to integers, but when I attempt to convert them in the QGIS field calculator, I get the following error:

Eval Error:
Cannot convert '26001980100' to int

The value in the error code above is the value from the first row in the field. I though this might be due to a field width constraint in shapefiles for integer columns, so I then imported the shapefile into a spatialite database and tried again without success. In both cases I was trying to create a new field populated by the converted values. How can I convert this field to integer?

The problem appears to goes deeper: after creating a new field and populating it with the integer values via the spatialite command line tool, QGIS does not then "see" the new field in the updated table. Am I out of luck with long integers in QGIS?

QGIS 2.12.3

Edit: I'm not sure whether this is a duplicate because the error appears specific to cases of converting strings having a length greater than 10 to integer. I tried the answer supplied here to convert to integer without luck. However, I was able to use that answer to convert the field to REAL, which is adequate for my purposes. The field in question is a foreign key, and the tables containing the primary key all store the values as INT, so I wanted to be consistent, but I can still join INT to REAL.

Answer 1

In spatialite you should be able to do a cast. Assuming a table "numbers" with one TEXT column, "number_str" (that represents integers) and a second column "number_ints" that was defined as INTEGER, then

UPDATE numbers SET number_ints=(CAST(number_str AS INTEGER));

More details on the sqlite concept of "affinity" and the CAST function are here

Regarding your "disappearing" column, this may be the same bug reported in the QGIS issue tracker. Something weird with creating columns in a spatialite DB, not through the QGIS interface, but it will get solved eventually. Here's the post on the spatialite-user's maillist explaining the workaround. It involves running

SELECT InvalidateLayerStatistics();
SELECT UpdateLayerStatistics();