SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



How to Extract Filename from Path using SQL Functions


In this sql tutorial, t-sql developers can find sql codes for extracting file name from fullpath of a file.
No matter whether the file path is stored in a sql table column or full filepath is supplied as a sql variable or sql string parameter, sql developers can use either sql functions to parse file name or use sql string functions in order to extract file name.

Here is sample t-sql code that sql developers can use to extract filename from path given as sql string variable or extract filename from path stored in sql table column.
In this SQL SELECT statement, sql string functions like CHARINDEX, SUBSTRING and REVERSE functions are used.

Let's create a sample sql database table and insert sample records for the sql sample to extract file name from file path.

Create Table FileSystem (
  FileID int identity(1,1),
  FullFilePath nvarchar(1000)
)

INSERT INTO FileSystem VALUES (
 N'D:\SQLDatabases\SQLSampleDatabase.mdf'
);
INSERT INTO FileSystem VALUES (
 N'C:\Program Files\Microsoft SQL Server\100\Samples\License.rtf'
);

Here is the sample sql codes which works on the above sql sample data and parse filename from full filepath.

SELECT
 LTRIM(
  RTRIM(
   REVERSE(
    SUBSTRING(
     REVERSE(FullFilePath),
     0,
     CHARINDEX('\', REVERSE(FullFilePath),0)
    )
   )
  )
 )
FROM FileSystem

And the output of the above sql script which sql parse file path string value for file name is as follows.

t-sql-parse-file-name-from-file-path





Here is user function source code of sample sql functions to extract file name from path.

ALTER FUNCTION GetFileName
(
 @fullpath nvarchar(max),
 @delimiter nvarchar(100)
) RETURNS nvarchar(max)
AS
BEGIN

declare @split as table (
 id int identity(1,1),
 fragment nvarchar(max)
)
declare @filename nvarchar(max)
declare @xml xml

SET @xml =
 N'<root><r>' +
 REPLACE(@fullpath, @delimiter,'</r><r>') +
 '</r></root>'

INSERT INTO @split(fragment)
SELECT
 r.value('.','nvarchar(max)') as item
FROM @xml.nodes('//root/r') as records(r)

SELECT @filename = fragment
FROM @split
WHERE id = (SELECT MAX(id) FROM @split)

RETURN LTRIM(RTRIM(@filename))

END

Here is a sample sql function call which parse file name from a given file path and returns the file name to the sql user.

SELECT dbo.GetFileName(N'C:\SQLDatabases\SQLBackup.mdf',N'\') as [File Name]

And the above sample function call code returns the "SQLBackup.mdf" as the output for the SELECT statement.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems