Get string after nth instance of a character and before an instance of another character

Question

I have a table (documentfiles) full of network paths. I need to extract the document name from column 'destination' minus the file extension, and then insert this into a column in the same table.

For example, a sample path I would have is:

J:\RootFolder\Subfolder\Reference\481789.pdf

I need to extract 481789 and insert this into the next column along called 'column3'. The structure is the same on each path, so I'm looking for the string after the 4th instance of '\' and before the '.'

I'm sure there must be a more elegant way to tackle this, but I have tried the below so far and I have managed to trim off the extension and only the first instance of '\'. I am struggling with nesting CHARINDEX to get the 4th instance along.

I've also not got far enough along to then carry out the insert yet.

SELECT
  destination,
  SUBSTRING(destination, CHARINDEX('\', destination),
  CHARINDEX('.', destination) - CHARINDEX('\', destination)) AS substring
FROM documentfiles

Answer 1

In sql server you can do

DECLARE @t varchar(100) = 'J:\RootFolder\Subfolder\Reference\481789.pdf';
SELECT
LEFT(reverse(left(reverse(@t), charindex('\', reverse(@t)) -1)),
  charindex('.',reverse(left(reverse(@t), charindex('\', reverse(@t)) -1))) - 1)

(No column name)
481789

DECLARE @t varchar(100) = 'J:\RootFolder\Subfolder\Reference\481789.pdf';
WITH ext AS (
  SELECT reverse(left(reverse(@t), charindex('\', reverse(@t)) -1)) as a)
SELECT LEFT(a,charindex('.',a)-1) FROM ext

(No column name)
481789

fiddle