1

I have an sqlite3 database. One of the columns has mostly integer values (either positive or negative). In a few cases, the value is "not found" or "missing" or other text strings.

I want to replace all those non-integer values with NULL. The column type is TEXT out of an original necessity.

Is there a clever way to do that with a UPDATE...WHERE clause instead of something gross, like pulling it into python to correct?

user3556757
  • 3,469
  • 4
  • 30
  • 70

2 Answers2

3

You can use UPDATE with regex, like this:

UPDATE MyTable
SET text_column = NULL
WHERE text_column REGEXP '[^-0-9]+'

This would clear out all fields that contain one or more non-digit character.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 2
    I would also suggest changing the type of the column to a number after doing this. – Gordon Linoff Nov 06 '15 at 12:58
  • REGEXP is not implemented by default. – CL. Nov 06 '15 at 13:24
  • Yeah, this looked like a slick solution until I discovered REGEXP is not a default function. Haven't found clear instructions on how to install it so that I can use call it from the command-shell as well as access through my python scripts. – user3556757 Nov 06 '15 at 13:37
  • @user3556757 [This Q&A](http://stackoverflow.com/q/5071601/335858) shows how to do it on a UNIX machine. – Sergey Kalinichenko Nov 06 '15 at 13:42
2

You can update all rows where the value cannot be converted into an integer:

UPDATE MyTable
SET MyColumn = NULL
WHERE CAST(MyColumn AS INTEGER) != MyColumn;
CL.
  • 173,858
  • 17
  • 217
  • 259