Comma to point to show decimal in SqLite3

Question

I've got an SQLite database that I populate directly from txt files. However, my textfiles has commas to show decimal. After insepcting the already appointed records, this leads to confusion as SQLite don't interpret these numbers correctly.

Is it possible to change records with a comma to a point in place (or should I rather populate the database over again?

Answer 1

If you want to have repeatable and consistent processes, you should fix your import and execute it again.

If you want to change the characters in place, use the replace() function:

UPDATE MyTable
SET MyColumn = replace(MyColumn, ',', '.')
WHERE MyColumn LIKE '%,%';

If you want the result to be numbers, you also have to change the type with CAST:

UPDATE MyTable
SET MyColumn = CAST(replace(MyColumn, ',', '.') AS NUMERIC)
WHERE MyColumn LIKE '%,%';