Oracle SQL – Removing Non-Numeric Characters

SQL (Structured Query Language, Oracle, SQL, Removing Non-Numeric Characters

SQL (Structured Query Language)

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>>,

Related Posts

Advertisements