An Integer Check That Doesn’t Blow Up

Sometimes you load in data sourcing from charactor data into a numeric or integer field.  The challenge is any non-numeric data will blowup your entire SQL with something like an atoi (alpha to integer) error.  You really need to check the data or do something to prevent the one bad element from blowing up everything.

My favorite method is actually to use the Netezza supplied SQLToolkit and use a simple regular expression to only load a numeric.  An example is

select regexp_extract(my_source_column,’^[0-9]{1,18}$’)

from …

This will only return an integer of up to 18 digits. You can also use this in a condition by comparing it back to the original source column.

Of course often the SQLToolkit is not installed or there is a desire to not use regular expressions.   You can use a test method of doing a to_number , cast back to a varchar, compare to the original and use an istrue command to return a true or false.

Select ..

From..

When

istrue(cast(to_number(sourcecolumn,’999999999999999999′) as varchar(20)) = sourcecolumn)

Conversely you can use the statement in your selected columns and use the true or false returned in a case statement if you wish to handle the non-numeric somehow, like return zero.

Below are some test examples to see how it works.

select istrue(cast(to_number(’124b5′,’999999999999999999′) as varchar(20)) = ’124b5′)

select istrue(cast(to_number(’1245′,’999999999999999999′) as varchar(20)) = ’1245′)

This entry was posted in SQL Tricks and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>