While I was preparing an article, I faced the challenge to extract the path from a filename in SQL Server. It’s an interesting challenge with many possible uses, so I decided to create a function to solve this problem.
The Main Expression
First, let’s understand the main expression to solve the problem. The path is everything before the last backslash (‘\’) in the full file name. So, we can proceed this way:
- Reverse the full filename
- Get the CharIndex of the first backslash (‘\’)
- Get the length of the full filename subtracted by the CharIndex of the first backslash
- From the left of the full file name, get the number of characters calculated by the previous formula
Creating the function
The next step is creating a function to solve this problem, so we don’t need to repeat the expression all the time.
The function will be like this:
returns VARCHAR(500)
AS
BEGIN
DECLARE @result VARCHAR(500)
SELECT @result = LEFT(@FullName, Len(@FullName) – Charindex(‘\’, Reverse(
@FullName)))
RETURN @result
END
In order to test the function, we can execute the following instruction:
dbo.Pathfromfullname(‘C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2\MSSQL\Log\system_health_0_131996396680890000.xel’)
Increasing the Safety
If the parameter has no backslash, the function will fail. A simple check can increase the safety of the function, but we can also turn this into another function that may be used in many places:
returns BIT
AS
BEGIN
DECLARE @result BIT
IF Charindex(‘\’, @FullName) = 0
SET @result=0
ELSE
SET @result=1
RETURN @result
END
Let’s fix the PathFromFullName function:
returns VARCHAR(500)
AS
BEGIN
DECLARE @result VARCHAR(500)
IF ( dbo.Isfullpath(@FullName) = 1 )
SELECT @result = LEFT(@FullName, Len(@FullName) –
Charindex(‘\’, Reverse
(
@FullName
)))
RETURN @result
END
Now if the parameter is not a full filename the result will be null
Load comments