SQL Trim on a file name

Question

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

@GordonLinoff just wanted to get the output after the last \ and take out the .xlsx part from the end — Minhal, Jan 14 '20 at 18:10
Do all your filenames end with .xlsx? Do any of the files have a different extension, do any of the extensions have three characters instead of four? — Derrick Moeller, Jan 14 '20 at 18:11
@DerrickMoeller yes all file names end with the same extension and with the same number of characters — Minhal, Jan 14 '20 at 18:14
@gbeaven yes they are but the file names are different and the file path remains the same — Minhal, Jan 14 '20 at 18:24

Suraj Kumar · Answer 1 · 2020-01-14T18:21:21.153

1

You can try this it will work as said in the comment the file extension are fixed.

SELECT 
Replace(
    RIGHT('\' + RTRIM('sasa\sasas\sasas\1234_IASS.xlsx'), CHARINDEX('\', REVERSE('\' + RTRIM('sasa\sasas\sasas\1234_IASS.xlsx'))) - 1)
          ,'.xlsx', '') 
as FileName

You can find the live example here.

edited Jan 14 '20 at 18:21

answered Jan 14 '20 at 18:17

Suraj Kumar

5,547
8
20
42

Hi, I just used this file name as an example but when extracting the data from the table all file names are different. So how will I use this ? – Minhal Jan 14 '20 at 18:20
by doing this way it replaces all the file names with the name 1234_IASS – Minhal Jan 14 '20 at 18:22
@Minhal the same will work as it is also working for the example: sa sa\s asas\sasa s\1234_IASS.xlsx – Suraj Kumar Jan 14 '20 at 18:23
2

@Minhal pass the field as the filepath, don't leave it hardcoded like this answer suggests. – gbeaven Jan 14 '20 at 18:23
@gbeaven Yes for calling on a table the column name of file path needed to supply. – Suraj Kumar Jan 14 '20 at 18:24
1

@Minhal Thanks for let me know and accepting the answer. – Suraj Kumar Jan 14 '20 at 18:28

Ryan Wilson · Answer 2 · 2020-01-14T18:31:24.297

Your post's title is misleading, TRIM is performed in sql-server to remove whitespace either by RTRIM or LTRIM or a combination of the two, what you are trying to do is get a substring out of your example string, I am providing a solution which uses a combination of REVERSE, SUBSTRING and CHARINDEX, this answer is good for if you ever need to do this for different file extensions:

DECLARE @test varchar(255) = 'sasa\sasas\sasas\1234_IASS.xlsx';
DECLARE @lastOccurance INT = CHARINDEX('\', REVERSE(@test)); --Last occurence of the slash character to denote the end of the directory name or what not
DECLARE @lastPeriod INT = CHARINDEX('.', REVERSE(@test)); --This is the last occurence of the period, denoting the file extension
SET @lastOccurance = LEN(@test) + 1 - @lastOccurance;
SET @lastPeriod = LEN(@test) + 1 - @lastPeriod;
SELECT SUBSTRING(@test, @lastOccurance + 1, @lastPeriod - (@lastOccurance + 1));

score 1 · Answer 3 · answered Jan 14 '20 at 18:24

1

You say your directory is the same and the extension is always the same? Replace [path] with your table column name:

select replace(replace([path],'sasa\sasas\sasas\',''),'.xlsx','')

answered Jan 14 '20 at 18:24

gbeaven

1,522
2
20
40

Derrick Moeller · Accepted Answer · 2020-01-14T18:48:24.370

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

score 1 · Answer 5 · answered Jan 14 '20 at 18:27

1

declare @x varchar(100) = 'sasa\sasas\sasas\1234_IASS.xlsx'

declare @filename varchar(max) = reverse(substring(reverse(@x), 1, charindex('\', reverse(@x))-1 ))
select substring(@filename, 1, charindex('.', @filename)-1)

answered Jan 14 '20 at 18:27

Daniel N

1,122
1
8
14

score 1 · Answer 6 · edited Jan 14 '20 at 19:33

1

If you want to remove extensions from filename then you can try this:

UPDATE TableName
SET FileName = REVERSE(SUBSTRING(REVERSE(FileName), 
                       CHARINDEX('.', REVERSE(FileName)) + 1, 999))

edited Jan 14 '20 at 19:33

Suraj Kumar

5,547
8
20
42

answered Jan 14 '20 at 18:54

Ankit Yadav

62
7

SQL Trim on a file name

6 Answers6