JTV8VKZq

1IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'IF' AND object_id = object_id('dbo.ParseFilePath'))
2    EXEC ('CREATE FUNCTION dbo.ParseFilePath() RETURNS TABLE AS RETURN SELECT Result = ''This is a stub'';' )
3GO
4
5
6ALTER FUNCTION dbo.ParseFilePath (@FilePath nvarchar(300))
7RETURNS TABLE
8/*************************************************************************************************
9AUTHOR: Andy Mallon
10CREATED: 20180114
11    Parses a full file path into separate file & path values. 
12    Also include the bare file name & file extension, because why not?
13PARAMETERS:
14    @FilePath - Text string of a complete file & path
15EXAMPLES:
16* 
17**************************************************************************************************
18MODIFICATIONS:
19    20160218 - 
20**************************************************************************************************
21    This code is free to download and use for personal, educational, and internal 
22    corporate purposes, provided that this header is preserved. Redistribution or sale, 
23    in whole or in part, is prohibited without the author's express written consent.
24    ©2014-2018 ● Andy Mallon ● am2.co
25*************************************************************************************************/
26AS
27RETURN
28    WITH ParseInfo AS(
29        SELECT FilePath      = @FilePath,
30               PathLen       = LEN(@FilePath),
31               FinalSlashPos = CHARINDEX('\', REVERSE(@FilePath), 1)
32        ),
33        ParsedPaths AS (
34        SELECT DirectoryPath = LEFT (FilePath, PathLen - FinalSlashPos + 1),
35               FullFileName  = RIGHT(FilePath, FinalSlashPos - 1),
36               FileExtension = RIGHT(FilePath, CHARINDEX('.', REVERSE(FilePath)) -1),
37               *
38        FROM ParseInfo
39        )
40    SELECT DirectoryPath,
41           FullFileName,
42           BareFilename = LEFT(FullFilename,LEN(FullFilename)-(LEN(FileExtension)+1)),
43           FileExtension
44    FROM ParsedPaths;
45GO