CAST TEXT as INTEGER

Question

I have a CHAR column that contains messy OCR'd scan of printed integers.

I need to do SUM() operators on that column. But I'm unable to cast properly.

;Good
sqlite> select CAST("123" as integer);
123

;No Good, should be '323999'
sqlite> select CAST("323,999" as integer);
323

I believe SQLite interprets the comma as marking the end of the "the longest possible prefix of the value that can be interpreted as an integer number"

I prefer to avoid the agony of writing python scripts to do data cleaning on this column. Is there any clever way to do it strictly with SQL?

Answer 1

If you are trying to ignore commas, then remove them before the conversion:

select cast(replace('323,999', ',', '') as integer)