Snowflake Extract Numbers using Regular Expression Functions

There are various requirements to extract numbers from string value. This requirement comes when you work with different data source. For example, heterogeneous data may contain many unwanted values and requirements will be to get only numbers or numeric values. Snowflake supports many built-in and regular expression functions. In this article, we will check how to extract numbers or numeric values from a string using Snowflake regular expression functions.

Extract Numbers using Snowflake Regular Expression Functions

In my other article, we have discussed on Snowflake regular expressions. We will use those functions to get a number or numeric values from a string.

Here are some of the common uses of Snowflake regular expressions with some examples.

Snowflake Extract Numbers from the string examples

The regular expression functions come handy when you want to extract numerical values from the string data. Though you can use built-in functions to check if a string is numeric. But, getting particular numeric values is done easily using regular expressions.

For example, extract the number from the string using Snowflake regexp_replace regular expression Function.

SELECT  TRIM(REGEXP_REPLACE(string, '[^[:digit:]]', ' ')) AS Numeric_value
FROM (SELECT ' Area code for employee ID 112244 is 12345.' AS string) a;

+-----------------+
| NUMERIC_VALUE   |
|-----------------|
| 112244    12345 |
+-----------------+

Note that, TRIM function is used to remove the leading space from the result.

Similarly, you can use different regex pattern to extract numbers or numeric values from strings.

For example, consider below query that uses different regex patterns.

SELECT  TRIM(REGEXP_REPLACE(string, '[a-z/-/A-z/./#/*]', '')) AS Numeric_value
FROM (SELECT ' Area code for employee ID 112244 is 12345.' AS string) a;

+---------------+
| NUMERIC_VALUE |
|---------------|
| 112244  12345 |
+---------------+

Snowflake Extract 6 digit’s numbers from string value examples

The most common requirement in the data warehouse environment is to extract certain digits from the string.

For example, extract the 6 digit number from string data. There are many methods that you can use, however, the easiest method is to use the Snowflake REGEXP_SUBSTR regular expressions for this requirement. You can modify the regular expression pattern to extract any number of digits based on your requirements.

SELECT REGEXP_SUBSTR(string, '(^|[^[:word:]]|[[:space:]])\\d{6}([^[:word:]]|[[:space:]]|$)') AS ID
FROM (SELECT ' Area code for employee ID 112244 is 12345.' AS string) a;

+----------+
| ID       |
|----------|
|  112244  |
+----------+

If you know the regex, you can use the different pattern based on your requirements.

Another common requirement is to extract alphanumeric values from a string data.

Snowflake Extract Alphanumeric from the string examples

For example, consider below example to extract ID which is a combination of ‘ID’ and numeric value.

SELECT REGEXP_SUBSTR('abc jjs Updates ID 123 ID_112233','ID_[0-9][0-9][0-9][0-9][0-9][0-9]') as ID;

+-----------+
| ID        |
|-----------|
| ID_112233 |
+-----------+