adf function to read only certain portion of filename based on pattern

Question

I have file names as SMP_ACC_STG_20210987654.txt and another filename SMP_ACC_STG_BS_20210987654.txt. I can use @substring(item().name,0,11) and i get SMP_ACC_STG for first file which is correct but for second file I need to get filename as SMP_ACC_STG_BS and it returns same file name as first because i have harcoded the length in substring. I tried using indexof but it didnt give me the expected result.

I need to extract the text before _20210987654.txt and use that as filename.

Answer 1

I have used the below, and got my file names:

@substring(item().name,0,lastindexof(item().name,'_'))

Which gave me:

SMP_ACC_STG
SMP_ACC_STG_BS