Skip to content
Advertisement

Remove varchar data with non-numeric characters, then convert to numeric data

I have a column where most values are numeric but a few contain letters or special characters:

Records
90000
5200
unknown
approximately 25 areas
TBC
5000 (approx)

I would like to remove any non-numeric entries and replace them with null like this:

Records
90000
5200
NULL
NULL
NULL
NULL

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.

Advertisement

Answer

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
6 People found this is helpful
Advertisement