Find index of the last occurrence of a substring using T-SQL

Extract the file name from the file path



DECLARE @PATH nvarchar(500)
DECLARE @FILENAME nvarchar(500)
DECLARE @POS INT

SET @PATH = 'D:\wwwroot\tech\sql'

SET @POS = LEN(@PATH) - CHARINDEX('\',REVERSE(@PATH)) + 2

SET @FILENAME = SUBSTRING(@PATH, @POS, 500)


PRINT CONVERT(VARCHAR(30), @POS) + ' ' + @FILENAME