Search code examples
sqlsql-servertrim

SQL Trim on a file name

I am trying to trim a long file name and just want to get the last characters of that file name which is after the last forward slash (\) and I also want to eliminate the .xlsx at the end of the file name as well.

Original:

sasa\sasas\sasas\1234_IASS.xlsx

Expected Output:

1234_IASS
Solution

  • Your comments state that both your file path and file extension are constant. If the number of characters in your file is also constant the simplest solution is to use SUBSTRING.

    SELECT SUBSTRING(YourColumn, 18, 9)
FROM YourTable

    If the number of characters is changing, a more robust solution is to use RIGHT to extract the file name and REPLACE to remove the file extension.

    SELECT REPLACE(RIGHT(YourColumn, LEN(YourColumn) - 17), '.xlsx', '')
FROM YourTable

    If you need a more dynamic solution, you can first extract the filename as shown.

    SELECT RIGHT(YourColumn, CHARINDEX('\', REVERSE(YourColumn)) - 1)
FROM YourTable

    You can then combine this with REPLACE as before to remove the extension.

    SELECT REPLACE(RIGHT(YourColumn, CHARINDEX('\', REVERSE(YourColumn)) - 1), '.xlsx', '')
FROM YourTable