Snowflake Extract Date using Regular Expression Functions

Post author:Vithal S
Post last modified:January 11, 2020
Post category:Snowflake
Reading time:5 mins read

In my other article, we have seen how to extract number or numeric values from a string. The other common requirement is to extract date from a string data. For example, you may get requirement to extract date of birth from the string field. Snowflake supports various date functions to validate a date value. In this article, we will check how to extract date from a string field using Snowflake regular expression functions.

Snowflake Extract Date using Regular Expression Functions

Extract Date using Snowflake Regular Expression Functions

In my other article, we have discussed on Snowflake regular expressions. We will use those functions to get a date values from a string. We will also use the built-in conversion function to convert date filed to the desired format.

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

Extract Date using Snowflake REGEXP_REPLACE Function

The REGEXP_REPLACE function is one of the easiest functions get required value. The idea here is to replace all the alphabetical characters except numbers and date delimiter.

For example, consider below snowflake example to replace all characters except date value.

SELECT  TRIM(REGEXP_REPLACE(string, '[a-z/-/A-z/.]', '')) AS date_value
FROM (SELECT 'Employee dob is 10-12-2019.' AS string) a;

+------------+
| DATE_VALUE |
|------------|
| 10-12-2019 |
+------------+

You can also use built-in functions such as TO_DATE and TO_CHAR to convert to required format.

For example, convert extracted date field to YYYY-MM-DD format.

SELECT  TRIM(TO_CHAR(TO_DATE(REGEXP_REPLACE(string, '[a-z/-/A-z/.]', ''),'DDMMYYYY'),'YYYY-MM-DD')) AS DATE_VALUE
FROM (SELECT 'Employee dob is 10/12/2019.' AS string) a;

+------------+
| DATE_VALUE |
|------------|
| 2019-12-10 |
+------------+

Extract Date using Snowflake REGEXP_SUBSTR Function

You can also use the Snowflake REGEXP_SUBSTR function to get date field from the string data.

For example, consider below example to get date value.

SELECT REGEXP_SUBSTR('Employee dob is 10/12/2019','[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]') as dob;

+------------+
| DOB        |
|------------|
| 10/12/2019 |
+------------+

You can convert the date filed to required format using built-in conversion functions.

For example, convert extracted date field to YYYY-MM-DD format.

SELECT TO_CHAR(TO_DATE(REGEXP_SUBSTR('Employee dob is 10/12/2019','[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'),'DD/MM/YYYY'),'YYYY-MM-DD') as dob;
+------------+
| DOB        |
|------------|
| 2019-12-10 |
+------------+

If you are familiar with the regular expression, then you can use different pattern based on your required date format. For example, you may get requirement to extract the date with timestamp format.