The following Microsoft SQL Server T-SQL queries will separate the filename from the pathname:

-- SQL parse path string to return filename

SELECT FileName=reverse(left(reverse('\\PRODSERVER\D$\EXPORT\Data20160401.txt'),

                    charindex('\',reverse('\\PRODSERVER\D$\EXPORT\Data20160401.txt'),

                             1) - 1))

GO

/* Result

FileName

Data20160401.txt

*/

-- SQL parse path for filename

DECLARE @PathFile varchar(512)

SET @PathFile = '\\PRODSERVER\D$\EXPORT\INVENTORYFEED20141205.csv'

SELECT FileName=reverse(left(reverse(@PathFile),

                    charindex('\',reverse(@PathFile),

                              1) - 1))

GO

/*

FileName

INVENTORYFEED20141205.csv

*/

-- SQL parse path string to return pathname only

DECLARE @PathFile varchar(512)

SET @PathFile = '\\PRODSERVER\D$\EXPORT\INVENTORYFEED20141205.csv'

SELECT PathName=LEFT(@PathFile, 

                    LEN(@PathFile) - charindex('\',reverse(@PathFile),1) + 1)

GO

/*

PathName

\\PRODSERVER\D$\EXPORT\

*/

-- SQL Server PARSENAME function
USE AdventureWorks2008;
SELECT PARSENAME('AdventureWorks2008.Sales.SalesOrderHeader', 1) AS 'Object Name';
-- SalesOrderHeader
SELECT PARSENAME('AdventureWorks2008.Sales.SalesOrderHeader', 2) AS 'Schema Name';
-- Sales
SELECT PARSENAME('AdventureWorks2008.Sales.SalesOrderHeader', 3) AS 'Database Name;'
-- AdventureWorks2008
SELECT PARSENAME('AdventureWorks2008.Sales.SalesOrderHeader', 4) AS 'Server Name';
-- NULL
GO

Related articles:

How to Extract Filename from Path using SQL Functions

An Easy Way to Get a File Name or a File Extension from a User Defined File Path using Transact-SQL