Search code examples
regexsnowflake-cloud-data-platformsnowsql

Invalid regular expression error in snowflake

I'm trying to extract date (20200222) from filename while copyinto command in snowflake.

File Name

s3://hadoop/2020002/dbo.DOSSIER_TRANSPORT_20200222.csv.gz

snowflake query

SELECT regexp_substr(metadata$filename, '/(_((\-|\+)?[0-9]+(\.[0-9]+)?).)/' as data FROM '@PRD.OPE_STG
                                                _CMD.SPX_PRD_CMD/' (file_format => 'OTS_TEST.OPA_STG_BENE.OTD_FORMAT', pattern => '.*dbo.DOSSIER_TRANSPORT.*') ;

I tried this regex but its not supporting in snowflake. Getting below error

100048 (2201B): Invalid regular expression: '/(_((-|+)?[0-9]+(.[0-9]+)?).)/', no argument for repetition operator: +
Solution

  • Use

    REGEXP_SUBSTR(metadata$filename, '_([0-9]+)[.]', 1, 1, 'c', 1)

    This is the regex demo

    The pattern matches _, then captures one or more digits in Group 1 and then matches .. Since the group_num argument is 1, the return value is Group 1 value.