By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tests(
path VARCHAR(100)
);
INSERT INTO tests VALUES
('Q:\12345\downloads\randomfilename.png'),
('Q:\123_4566\downloads\randomfilename.pdf'),
('Q:\CCCMUD\downloads\randomfilename.mp4'),
('Q:'),
('Q:\'),
('Q:\file.bmp'),
('Q:\test\file.bmp');
7 rows affected
SELECT *, SUBSTRING(path, pos1 + 1, pos2 - pos1 - 1)
FROM tests
CROSS APPLY (SELECT NULLIF(CHARINDEX('\', path), 0)) AS ca1(pos1)
CROSS APPLY (SELECT NULLIF(CHARINDEX('\', path, pos1 + 1), 0)) AS ca2(pos2)
| path | pos1 | pos2 | (No column name) |
|---|---|---|---|
| Q:\12345\downloads\randomfilename.png | 3 | 9 | 12345 |
| Q:\123_4566\downloads\randomfilename.pdf | 3 | 12 | 123_4566 |
| Q:\CCCMUD\downloads\randomfilename.mp4 | 3 | 10 | CCCMUD |
| Q: | null | null | null |
| Q:\ | 3 | null | null |
| Q:\file.bmp | 3 | null | null |
| Q:\test\file.bmp | 3 | 8 | test |