Change datatype of a whole column from string to integer in SQLite?

Question

I'm pretty new to SQL. I'm practicing on a dataset I downloaded, unfortunately some int type columns became varchar when I imported them. I need to change them back into int.

I tried to create a temp table, create the correct schema for the table with appropriate datatype for all columns, then dump everything back. I got an int column with a mixture of int and empty strings. How do I convert empty strings to zeros? Thank you.

forpas · Accepted Answer · 2020-02-19 17:20:05Z

1

With an UPDATE statement like this:

UPDATE tablename
SET columnname = 0
WHERE columnname is null or trim(columnname) = ''

Remove columnname is null if you don't have nulls but empty strings.

answered Feb 19, 2020 at 17:20

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change datatype of a whole column from string to integer in SQLite?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related