Removing part of a string - SQL Server

Question

I have written in a column of the database the name of a file with extension (FILE.txt). I need to show in a SELECT only the FILE instead of FILE.txt

Someone knows how to do?

Answer 1

If you know exactly which extension, you can use REPLACE

SELECT REPLACE(file, '.txt', '') FROM table;

https://msdn.microsoft.com/en-us/library/ms186862.aspx

or, if they are multiple extensions, then a SUBSTRING is better:

https://msdn.microsoft.com/en-us/library/ms187748.aspx

SELECT SUBSTRING(file, -4, 4) FROM table;

Answer 2

I’ve made an example that covers a lot of cases. Example: .txt file, .secreto.txt file, .jpeg file.

Declare @campo varchar(100) = 'Arquivo.txt'
select left(@campo,len(@campo) - charindex('.',reverse(@campo)) ) resultado