Step 2) Parsing the file & path from the full path.

ParseFilePath-GetNameAndPath.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)
    )
SELECT DirectoryPath = LEFT (FilePath, PathLen - FinalSlashPos + 1),
        FullFileName  = RIGHT(FilePath, FinalSlashPos - 1),
        *
FROM ParseInfo;