I have a column say LINES with the below string patters. I want to extract the date from the strings. For example for each lines I would need the date i.e 20201123 or 20201124 whichever the case may be.
Since the dates are in different positions I can't really use substring for this. How do I go about this ? Is there a simpler REGEX method within substring that I can apply to this.
Here is a simple reproduced code for testing.
create volatile table TEST
(LINES VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC)
ON COMMIT PRESERVE ROWS;
insert into TEST values('path/to/file/OVERALL_GOTO_Datas.20201123.dat');
insert into TEST values('path/to/file/endartstmov20201124.20201124.dat');
insert into TEST values('path/to/file/TESTDEV20201123.20201123.5.0014.CHK.dat');
insert into TEST values('path/to/file/DEVTOTES20201124.20201124.5.0109.CHK.dat');
insert into TEST values('path/to/file/STORE_PARTNER.20201124.20201124.0.0501.CHK.dat');
SELECT * FROM TEST;
Appreciate your responses. Thanks.