Sometimes, when converting fields from text to numeric fields in SQL, there is a need to remove one or more non-numeric values. These approaches have been useful for handling multiple non-numeric values.
Removing non-numeric values – Approach 1:
I have found this approach useful when working with character (Char, Varchar, NVARCHAR) fields being converted to non-integers outputs (e.g. Decimal).
CAST(( CASE WHEN TRIM(<<SourceFieldName>>) > ‘9999999999’ or length(REGEXP_SUBSTR(<<SourceFieldName>>, ‘[A-z]’, 1, 1))>0 or trim(<<SourceFieldName>>) in (‘-‘,’+’,’?’,’/’,’*’) THEN NULL ELSE trim(<<SourceFieldName>>) END) AS DECIMAL(10,2)) AS <<TargetFieldName>>,
Removing non numeric values – Approach 2:
If you need to know if a content of a field is numeric, I have most this approach useful when working with character (Char, Varchar, NVARCHAR) fields being converted to integers:
CAST(( CASE WHEN LENGTH(TRANSLATE(<<SourceFieldName>>,’0123456789′,”))=0 THEN (<<SourceFieldName>> ELSE NULL END) as INTEGER) as <<TargetFieldName>>,
