Extract string between two characters in a string

I have a set of strings that has datetime values and I would like to extract them. I am not sure if this is even possible using T-SQL.

CREATE TABLE #Temp (
BLOB_NM VARCHAR(100)
);

INSERT INTO #Temp
SELECT 'products_country_20200528102030.txt'
UNION ALL
SELECT 'products_territory_20190528102030.txt'
UNION ALL
SELECT 'products_country_2020-05-20_20200528102030.txt'
;

Expected Results:

20200528102030
20190528102030
20200528102030

Answer

For this dataset, string functions should do it:

select blob_nm, substring(blob_nm, len(blob_nm) - 17, 14) res from #temp

The idea is to count backwards from the end of the string, and capture the 14 characters that preced the extension (represented by the last 4 characters of the string).

Demo on DB Fiddle:

blob_nm                                        | res           
:--------------------------------------------- | :-------------
products_country_20200528102030.txt            | 20200528102030
products_territory_20190528102030.txt          | 20190528102030
products_country_2020-05-20_20200528102030.txt | 20200528102030