Tuesday, August 7, 2012

How to get Filename and File Extension from a Column with tSQL

I have written this query like 100 times, but this time I am going to document it so I don't lose it again.

This query specifically assumes that you have a single column that contains a file name with or without an extension, but not the full path. I'm sure modifying this query to accommodate any needs shouldn't be that difficult. I will be the first to say I am not very proud of how inefficient this query is, but the point is it works. If you are going for performance, then you might want to use a while loop or a cursor to iterate through each row while storing results in a variable.

I was using this query to move data from one table to another for a one time fix in my database.

SELECT 
 OriginalFileName,
 FileName = LEFT(OriginalFileName, LEN(OriginalFileName) - CHARINDEX('.', REVERSE(OriginalFileName), 0)),
 Extension = REVERSE(LEFT(REVERSE(OriginalFileName), CHARINDEX('.', REVERSE(OriginalFileName), 0) - 1))
FROM YourTableHere

FileName = LEFT(OriginalFileName, LEN(OriginalFileName) - CHARINDEX('.', REVERSE(OriginalFileName), 0))
This line is grabbing everything to the left of the last period in the string. I am utilizing the REVERSE() function in order to find the location of the last period (.) in the file name just in case the filename looks like this: "books_05.01.12.pdf " which can get confusing very quickly. Think of "CHARINDEX('.', REVERSE(OriginalFileName), 0))" as a LastIndexOf() function.

Extension = REVERSE(LEFT(REVERSE(OriginalFileName), CHARINDEX('.', REVERSE(OriginalFileName), 0) - 1))
This line is grabbing everything to the left of the last period in the reverse of the original string and finally it reverses the whole result to make it face the right way.

Again this methodology is very inefficient because I am repeating the same method calls several times on the exact same column. This could be easily fixed by implementing a loop or a cursor and only calling the repeated function calls once and storing the result in a variable.

This query will not work for SQL Server 2000.

No comments:

Post a Comment