Remove extensions from filename

Question

I'm using SQL.

In a table tblDemo,one of the column is 'FileName'. Each row of this column contains a different filename with any extension. For ex. 'flower.jpeg', 'batman.mov', study.pdf etc.

Please suggest me on a query which could help me to remove the extension(and dot as well) from each row of the 'filenames' column. So that I could fetch only the name Ex. 'flower', 'batman', 'study' etc.

Thanks

Answer 1

try this one out:

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

View For a DEMO @ SQLFiddle.com

Answer 2

Tested on Sql Server. This shows the filenames without extension, change to Update / Set to modify data.

SELECT left([FileName], len([FileName]) - charindex('.', reverse([FileName]))) 
  FROM tblDemo

Edited: modified using Reverse, so it also works when the field contains multiple dots.

Here the Update Table version:

UPDATE Testing 
   Set [FileName] = left([FileName], 
                         len([FileName]) - charindex('.', Reverse([FileName])))