How to extract the number from file link and convert it date

Rohit Jha 0
Viewed 29

I have one column name "link" having a data like:-

 Link
www_dump/e3_upoad/sdf/smc_sm_lys_vu_12221126-509
www_dump/e3_upoad/sdi/smc_sm_lys_vu_11342356-43445

The Output I want to extract the number and convert it to It is something like this

Link                                                 DATE
www_dump/e3_upoad/sdf/smc_sm_lys_vu_12221126-509  12221126
www_dump/e3_upoad/sdi/smc_sm_lys_vu_11342356-43445  11342356 
and convert it to date format.

I tried using substr but no luck

snowflake-cloud-data-platform
1 Answers

You can use REGEXP_SUBSTR to parse the text:

select link, TO_DATE( regexp_substr(link,'.*smc_sm_lys_vu_(\\d*)-.*',1,1,'e'), 'YYYYMMDD') "DATE"
from values 
('www_dump/e3_upoad/sdf/smc_sm_lys_vu_20220916-509'),
('www_dump/e3_upoad/sdi/smc_sm_lys_vu_20210801-43445') tmp(link);

+----------------------------------------------------+------------+
|                        LINK                        |    DATE    |
+----------------------------------------------------+------------+
| www_dump/e3_upoad/sdf/smc_sm_lys_vu_20220916-509   | 2022-09-16 |
| www_dump/e3_upoad/sdi/smc_sm_lys_vu_20210801-43445 | 2021-08-01 |
+----------------------------------------------------+------------+
Gokhan Atil0
Related