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 |
+------------+
Related Article,
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.
Related Articles,
- Snowflake Extract Numbers using Regular Expression Functions
- Snowflake Regular Expressions and Examples
Hope this helps 🙂