ParseFilePath-KeepParsing.sql

DECLARE @FilePath nvarchar(300) = N'C:\Users\Andy\Documents\GitHub\dba-database\stored-procedures\dbo.Alert_Blocking.sql';

WITH ParseInfo AS(
    SELECT FilePath      = @FilePath,
           PathLen       = LEN(@FilePath),
           FinalSlashPos = CHARINDEX('\', REVERSE(@FilePath), 1)
    ),
    ParsedPaths AS (
    SELECT DirectoryPath = LEFT (FilePath, PathLen - FinalSlashPos + 1),
           FullFileName  = RIGHT(FilePath, FinalSlashPos - 1),
           FileExtension = RIGHT(FilePath, CHARINDEX('.', REVERSE(FilePath)) -1),
           *
    FROM ParseInfo
    )
SELECT DirectoryPath,
       FullFileName,
       BareFilename = LEFT(FullFilename,LEN(FullFilename)-(LEN(FileExtension)+1)),
       FileExtension
FROM ParsedPaths;