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))