Search code examples
sql-serversubstringpatindex

SQL SUBSTRING & PATINDEX of varying lengths

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.

Solution

  • 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