Extract the Path from Filename in SQL

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:

CREATE FUNCTION dbo.Pathfromfullname (@FullName VARCHAR(500))
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:

SELECT
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:

CREATE FUNCTION dbo.Isfullpath (@FullName VARCHAR(500))
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:

ALTER FUNCTION dbo.Pathfromfullname (@FullName VARCHAR(500))
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

The post Extract the Path from Filename in SQL appeared first on Simple Talk.

from Simple Talk http://bit.ly/2IuLRIZ
via