对于此数据集，字符串函数应执行以下操作：

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

其思想是从字符串末尾开始倒数，并捕获扩展名之前的14个字符（由字符串的最后4个字符表示）。
db小提琴演示：

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

如果对助手函数感兴趣。。。我创建这个tvf是因为我对提取字符串的部分（左、右、charindex、反向、子字符串等）进行了分层
例子

Select * 
 From  #Temp A
 Cross Apply [dbo].[tvf-Str-Extract](Blob_NM,'_','.') B

退货

BLOB_NM                                         RetSeq  RetVal
products_country_20200528102030.txt             1       20200528102030
products_territory_20190528102030.txt           1       20190528102030
products_country_2020-05-20_20200528102030.txt  1       20200528102030

如果有兴趣的话

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
            Cross Apply XMLData.nodes('x') AS B(i)
          ) C1
    Where charindex(@Delim2,RetVal)>1

)

我想：
文件扩展名不总是3个字符长
日期/时间格式始终为14个字符
试试这个：

select 
CONVERT(DATETIME, STUFF(STUFF(STUFF(left(right(BLOB_NM, charindex('_', reverse(BLOB_NM) + '_') - 1), 14),13,0,':'),11,0,':'),9,0,' ')) as Result
from #Temp