Amazon Redshift isnumeric Alternative and Examples

Post author:Vithal S
Post last modified:November 20, 2019
Post category:Redshift
Reading time:6 mins read

When you work with heterogeneous data set, you may have to filter out unwanted data before loading it to the actual data warehouse table. For example, you many have field1 of type string contains alphanumeric values. You may get requirement to filter out non-numeric values. In this article, we will check Amazon Redshift isnumeric alternative with some examples.

Amazon Redshift isnumeric Function

Many databases such as SQL server supports isnumeric built-in functions. As of now, AWS Redshift does not support isnumeric function. You have to use an alternative approach such as using Redshift regular expression or creating user-defined function to identify numeric values.

There are many methods that you can use as an isnumeric function alternative.

For example,

Create user-defined Function.
Use Regular Expression.
Use SIMILAR TO.

Now let us verify the above approaches with an example.

Create isnumeric user-defined Function

Redshift supports user-defined functions. You can create Redshift UDF to check whether a given string value is numeric. This is one of the recommended methods.

create or replace function isnumeric (aval VARCHAR(300))
  returns bool
IMMUTABLE 
as $$
    try:
       x = int(aval);
    except:
       return (1==2);
    else:
       return (1==1);
$$ language plpythonu;

Output:

select isnumeric ('012345678910');
 isnumeric
-----------
 t
(1 row)

select isnumeric ('012345678910test');
 isnumeric
-----------
 f
(1 row)

Regular Expression to Identify Numeric

You can use Redshift regular expression to identify numeric values from the expression or input string.

REGEXP_COUNT(field_name, '^[0-9]+$')

If above regular expression returns count other than ‘0’ then field is numeric.

For example,

select REGEXP_COUNT('012345678910', '^[0-9]+$');
 regexp_count
--------------
            1
(1 row)

select REGEXP_COUNT('012345678910test', '^[0-9]+$');
 regexp_count
--------------
            0
(1 row)

SIMILAR TO Matching Condition

You can use Redshift pattern matching condition to identify the numeric values. The SIMILAR TO is one of the pattern matching condition that you can use.

expression [ NOT ] SIMILAR TO pattern [ ESCAPE 'escape_char' ]

For example,

 select '012345678910' SIMILAR TO '[0-9]+(.[0-9][0-9])?' as result;
 result
--------
 t
(1 row)

Hope this helps 🙂

Tags: Redshift, Regular Expressions, SQL