Removing part of a string - SQL Server

Question

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

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

Does anyone know how to do it?

sql

asked by anonymous 05.01.2017 / 19:05

2 answers

1

I've done an example that caters to several cases. Example: file.txt, file.secret.txt, file.jpeg.

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

05.01.2017 / 19:28

score 3 · Accepted Answer

If you know exactly which extension, you can use REPLACE

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

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

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