Scalar functions for UNC file handling

Two quick little scalar functions that strip out the filename and filepath from a fully qualified UNC filename.

Two quick little scalar functions that strip out the filename and filepath from a fully qualified UNC filename

.

i.e. @UNCPath = "ServerNameShareNameDirectoryFilename.txt"

SELECT dbo.fn_GetUNCFileName(@UNCPath)
returns "Filename.txt"

and

SELECT dbo.fn_GetUNCFilePath(@UNCPath)
returns "ServerNameShareNameDirectory" 
		
Code: 
CREATE FUNCTION fn_GetUNCFileName 
	(@FileString VARCHAR(8000))
RETURNS varchar(8000)
AS
BEGIN

DECLARE 
	@StartPointer 	INT,
	@NextPointer	INT,
	@FileName	VARCHAR(8000)

/** Strip Out filename from a UNC FilePath eg. 'TestDirectoryFilename.csv' **/

SELECT @StartPointer = CHARINDEX('',@FileString)

WHILE @StartPointer != 0
BEGIN
	SELECT @NextPointer = CHARINDEX('',@FileString,@StartPointer)

	-- Got start point of filename
	IF @NextPointer = 0
	BEGIN
		SELECT @FileName = SUBSTRING(@FileString,@StartPointer,(DATALENGTH(@FileString)-@StartPointer)+1)
		SELECT @StartPointer = @NextPointer 
	END
	ELSE
		SELECT @StartPointer = @NextPointer + 1

END

RETURN @FileName

END

GO


CREATE FUNCTION fn_GetUNCFilePath 
	(@FileString VARCHAR(8000))
RETURNS varchar(8000)
AS
BEGIN

DECLARE 
	@StartPointer 	INT,
	@NextPointer	INT,
	@FilePath	VARCHAR(8000)

/** Strip Out filepath from a UNC FilePath eg. 'TestDirectoryFilename.csv' **/

SELECT @StartPointer = CHARINDEX('',@FileString)

WHILE @StartPointer != 0
BEGIN
	SELECT @NextPointer = CHARINDEX('',@FileString,@StartPointer)

	-- Got start point of filename
	IF @NextPointer = 0
	BEGIN
		SELECT @FilePath = LEFT(@FileString,@StartPointer-2)
		SELECT @StartPointer = @NextPointer 
	END
	ELSE
		SELECT @StartPointer = @NextPointer + 1

END

RETURN @FilePath

END

GO


This was first published in May 2002

Dig deeper on .NET Development for SQL Server

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close