Snowflake isnumeric Alternative and Examples

The data warehouse combines the data from various sources. Sometimes, data will be corrupted or erroneous values are introduced. For example, you may need to filter out non-numeric values from the salary field. In this article, we will check what is the isnumeric function alternative available in Snowflake. We will also check methods that you can use to validate data or expression.

Snowflake isnumeric Function Alternative

In a relational database such as SQL Server, isnumeric function is available as a built-in numeric function. But, as of now, Snowflake does not support isnumeric function. You have to use the alternative method.

The good news is, Snowflake provide many other functions or methods that you can use to validate numeric fields.

Following are the methods that you can use as an isnumeric function alternative.

User Defined Functions
IS_REAL built-in Function
IS_DOUBLE built-in Function
Snowflake Regular Expression

Now, let us check these functions in brief.

User Defined Functions

You can create a user defined function to validate column data or expression.

For a simplicity, we have used Snowflake SQL UDF.

For instance, consider following UDF to check if input expression is numeric.

create function isnumeric(input VARIANT) 
  returns boolean
  as 
  $$ SELECT IS_REAL(TO_VARIANT(input)) $$
  ;

Now, call snowflake user defined function.

select isnumeric(95.5) is_numeric;
+------------+
| IS_NUMERIC |
|------------|
| True       |
+------------+

select isnumeric(to_variant('hello')) is_numeric;
+------------+
| IS_NUMERIC |
|------------|
| False      |
+------------+

Snowflake IS_REAL Function

The IS_REAL function in Snowflake returns TRUE if its VARIANT argument contains a floating-point value, fixed-point decimal, or integer.

Note that, it works only on VARIANT data type values or expression.

For example, consider following example to check expression is numeric. Firstly, we will convert the expression to variant type and then apply IS_REAL function.

Following example check if input value is integer.

SELECT IS_REAL(TO_VARIANT(100)) as integer;
+---------+
| INTEGER |
|---------|
| True    |
+---------+

Following example check if input value is numeric.

SELECT IS_REAL(TO_VARIANT(99.5)) as numeric;
+---------+
| NUMERIC |
|---------|
| True    |
+---------+

Now, let us check this with string value. It should return false value.

SELECT IS_REAL(TO_VARIANT('hello')) as str;
+-------+
| STR   |
|-------|
| False |
+-------+

Snowflake IS_DOUBLE Function

The IS_DOUBLE function in Snowflake returns TRUE if its VARIANT argument contains a floating-point value, fixed-point decimal, or integer.

Note that, it works only on VARIANT data type values or expression.

For example, consider following example to check expression is numeric. Firstly, we will convert the expression to variant type and then apply IS_DOUBLE function.

Following example check if input value is integer.

SELECT IS_DOUBLE(TO_VARIANT(100)) as integer;
+---------+
| INTEGER |
|---------|
| True    |
+---------+

Following example check if input value is numeric.

SELECT IS_DOUBLE(TO_VARIANT(99.5)) as numeric;
+---------+
| NUMERIC |
|---------|
| True    |
+---------+

Similarly, let us check this with string value. It should return false value.

SELECT IS_DOUBLE(TO_VARIANT('hello')) as str;
+-------+
| STR   |
|-------|
| False |
+-------+

Snowflake Regular Expression to Identify Numeric

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

For example, consider below SQL statement.

select REGEXP_COUNT('012345678910', '^[0-9]+$') as test;
+------+
| TEST |
|------|
|    1 |
+------+

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