I have a column where most values are numeric but a few contain letters or special characters:
|approximately 25 areas|
I would like to remove any non-numeric entries and replace them with null like this:
I tried to remove non-numerical characters first, then change the data to numeric:
SELECT "Year" ,regexp_replace("Records",'[%A-Za-z%+$]',NULL) FROM records_table
However this resulted in changing all records to NULL, not just non-numeric ones.
You could try keeping the value when the field “Records” matches numbers only:
SELECT "Year", CASE WHEN "Records" REGEXP '[0-9]+' THEN "Records" END FROM records_table