• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Stored Procedure to get Filename from UNC

Let's say we have the following table:

ID       file                                                                    date
1      \\server\share\folder1\folder2\blah.txt           01/01/2010
2     \\server\share\folder1\folder2\blah2.txt          01/01/2010
3     \\server\share\folder1\blah3.txt                       01/01/2010

I need a SQL Stored procedure such that I can get only the file name out of the full UNC - for example:

SELECT getFileName(file) as FileName, date as EntryDate
FROM myTable
WHERE date = 01/01/2010

FileName        EntryDate
blah.txt           01/01/2010
blah2.txt         01/01/2010
blah3.txt         01/01/2010

I'm using SQL Server 2005 and 2008.

Thank you all in advanced.
James
0
JamesNT
Asked:
JamesNT
  • 2
1 Solution
 
elimesikaCommented:
HI

You can do that in a simple select , assuming your table name is T
SELECT REVERSE(SUBSTRING(REVERSE([file]), 0, CHARINDEX('\', REVERSE([file]), 1))) [FileName] , date EntryDate from T

Open in new window

0
 
8080_DiverCommented:
Assuming you want to call the Stored Proc and get back the indicated results from it as a dataset, try the following:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Ralph D. Wilson II
-- Create date: 2009-12-23
-- Description:	Return filename extracted from 
--              UNC filename and EntryDate from 
--              data in  your table
-- =============================================
CREATE PROCEDURE usp_Extract_Filename_From_UNC_Filename 
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @MemTable TABLE 
		(
			EntryID		INT,
			UNCFileName	VarChar(128),
			EntryDate	DateTime
		);

	INSERT INTO @MemTable
	SELECT	EntryID,
			REVERSE(UNCFileName) AS UNCFileName,
			EntryDate
	FROM	YourTable;

	SELECT	EntryID,
			REVERSE(SUBSTRING(UNCFileName, 1, PatIndex('/', UNCFileName) - 1)) AS FileName,
			EntryDate
	FROM	@MemTable;
    
END
GO

Open in new window

0
 
elimesikaCommented:
You can also create a function to wrap that
CREATE FUNCTION [dbo].[fn_get_file_name]
(
	@file nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @result nvarchar(max)
select @result = REVERSE(SUBSTRING(REVERSE(@file), 0, CHARINDEX('\', REVERSE(@file), 1)))
	RETURN @result
END

Then you call it like this

SELECT dbo.fn_get_file_name([file]) , date EntryDate from T

Open in new window

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now