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

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.

CodePudding user response：

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

CodePudding user response：

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