CODEWITHSUNDEEP
sqlregexsnowflake-cloud-data-platform
Omnibus
Omnibus

Reputation: 23

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.

Upvotes: 1

Views: 81

Answers (2)

Motif
Motif

Reputation: 67

Good Day, I hope this code is useful for you.

SELECT RECORDS, REGEXP_REPLACE (RECORDS, '.+[a-zA-Z]\D', 'NULL') AS RESULT
FROM (SELECT '90000' AS RECORDS FROM DUAL
    UNION ALL
    SELECT '5200' AS RECORDS FROM DUAL
    UNION ALL
    SELECT 'approximately 25 areas' AS RECORDS FROM DUAL
    UNION ALL
    SELECT 'TBC' AS RECORDS FROM DUAL
    UNION ALL
    SELECT '5000 (approx)' AS RECORDS FROM DUAL)

You can try on Regex101

Upvotes: 0

lemon
lemon

Reputation: 15482

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

Upvotes: 0

Related Questions