SQL SUBSTRING & PATINDEX of varying lengths

Question

SQL Server 2017.

Given the following 3 records with field of type nvarchar(250) called fileString:

_318_CA_DCA_2020_12_11-01_00_01_VM6.log
_319_CA_DCA_2020_12_12-01_VM17.log
_333_KF_DCA01_00_01_VM232.log

I would want to return:

VM6
VM17
VM232

Attempted thus far with:

SELECT
SUBSTRING(fileString, PATINDEX('%VM[0-9]%', fileString), 3)      
FROM dbo.Table

But of course that only returns VM and 1 number.

How would I define the parameter for number of characters when it varies?

EDIT: to pre-emptively answer a question that may come up, yes, the VM pattern will always be proceeded immediately by .log and nothing else. But even if I took that approach and worked backwards, I still don't understand how to define the number of characters to take when the number varies.

Answer 1

here is one way :

DECLARE  @test TABLE( fileString varchar(500))

INSERT INTO @test VALUES 
 ('_318_CA_DCA_2020_12_11-01_00_01_VM6.log')
,('_319_CA_DCA_2020_12_12-01_00_01_VM17.log')
,('_333_KF_DCA_2020_12_15-01_00_01_VM232.log')


-- 5 is the length of file extension + 1 which is always the same size '.log'
SELECT
   REVERSE(SUBSTRING(REVERSE(fileString),5,CHARINDEX('_',REVERSE(fileString))-5))
FROM @test AS t

Answer 2

Your lengths are consistent at the beginning. So get away from patindex and use substring to crop out the beginning. Then just replace the '.log' with an empty string at the end.

select  *,
        part = replace(substring(filestring,33,255),'.log','')
from    table;

---

Edit:

Okay, from your edit you show differing prefix portions. Then patindex is in fact correct. Here's my solution, which is not better or worse than the other answers but differs with respect to the fact that it avoids reverse and delegates the patindex computation to a cross apply section. You may find it a bit more readable.

select      filestring,
            part = replace(substring(filestring, ap.vmIx, 255),'.log','')
from        table
cross apply (select
                vmIx = patindex('%_vm%', filestring) + 1
            ) ap

Answer 3

This will dynamically grab the length and location of the last _ and remove the .log.

It is not the most efficient, if you are able to write a CLR function usnig C# and import it into SQL, that will be much more efficient. Or you can use this as starting point and tweak it as needed.

You can remove the variable and replace it with your table like below

DECLARE @TESTVariable as varchar(500)
Set @TESTVariable = '_318_CA_DCA_2020_12_11-01_00_01_VM6adf.log'

SELECT REPLACE(SUBSTRING(@TESTVariable, PATINDEX('%VM[0-9]%', @TESTVariable), PATINDEX('%[_]%', REVERSE(@TESTVariable))), '.log', '')

select  *,
        part = REPLACE(SUBSTRING(filestring, PATINDEX('%VM[0-9]%', filestring), PATINDEX('%[_]%', REVERSE(filestring))), '.log', '')
from    table