16

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

Kings
  • 1,551
  • 12
  • 32
  • 52
  • 2
    this will help - http://stackoverflow.com/questions/1024978/find-index-of-last-occurrence-of-a-sub-string-using-t-sql – Kshitij Jun 28 '12 at 09:33

5 Answers5

44

try this one out:

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

View For a DEMO @ SQLFiddle.com

CervEd
  • 3,306
  • 28
  • 25
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    u r champ. It worked. It indeed removes the extension from fileName, even if the filename has multiple 'dot' in it. I will edit your post just a bit, so that it would help any other in future. – Kings Jun 28 '12 at 09:53
  • 5
    For MySQL users use LOCATE instead of CHARINDEX – Alfonso Fernandez-Ocampo Mar 18 '14 at 20:35
  • @JohnWoo whats the deal with the 999? – johnny 5 Sep 30 '20 at 19:22
  • 2
    @johnny5 999 is the start parameter for the string (filename). From https://www.w3schools.com/sql/func_sqlserver_charindex.asp: "The position where the search will start (if you do not want to start at the beginning of string). The first position in string is 1." If you have Strings inside your column that have more than 999 chars you have to modify this parameter. In most cases this number should be sufficient. – Douy789 Aug 25 '21 at 11:55
13

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])))
Jcis
  • 153
  • 2
  • 15
  • Ben/Jcis - what to modify in the mentioned query, to take account of the case similar to this:- 'This.Is.My.Filename.Txt' – Kings Jun 28 '12 at 09:40
  • @Jcis - Thanks a lot friend. Just accepted John's answer 'coz he responded first that multiple dots issue. Your answer is absolutley correct. – Kings Jun 28 '12 at 10:02
3

I needed to get rid of all extensions, i.e: .tar.gz or .txt.out. This is what worked for me in SQL Server:

CREATE FUNCTION RemoveFileExt
(
    @fullpath nvarchar(500)
)
RETURNS nvarchar(500)
AS
BEGIN
    IF(CHARINDEX('.', @fullpath) > 0)
    BEGIN
       SELECT @fullpath = SUBSTRING(@fullpath, 1, CHARINDEX('.', @fullpath)-1)
    END
    RETURN @fullpath
END;

CREATE FUNCTION RemoveFileExtAll
(
    @fullpath nvarchar(500)
)
RETURNS nvarchar(500)
AS
BEGIN
    IF(CHARINDEX('.', @fullpath) > 0)
    BEGIN
        SELECT @fullpath = dbo.RemoveFileExt(@fullpath)
    END
    RETURN @fullpath
END;

select dbo.RemoveFileExtAll('test.tar.gz');

OUTPUT> test

As a bonus, to get just the base name from the fully qualified path in Linux or Windows:

CREATE FUNCTION GetBaseName
(
    @fullpath nvarchar(500)
)
RETURNS nvarchar(500)
AS
BEGIN
    IF(CHARINDEX('/', @fullpath) > 0)
    BEGIN
       SELECT @fullpath = RIGHT(@fullpath, CHARINDEX('/', REVERSE(@fullpath)) -1)
    END
    IF(CHARINDEX('\', @fullpath) > 0)
    BEGIN
       SELECT @fullpath = RIGHT(@fullpath, CHARINDEX('\', REVERSE(@fullpath)) -1)
    END
    RETURN @fullpath
END;

select dbo.GetBaseName('/media/drive_D/test.tar.gz');

OUTPUT> test.tar.gz

select dbo.GetBaseName('D:/media/test.tar.gz');

OUTPUT> test.tar.gz

select dbo.GetBaseName('//network/media/test.tar.gz');

OUTPUT> test.tar.gz
Dan
  • 151
  • 6
0

Here is a simple select statement that returns the desired results:

 SELECT  [Filename], SUBSTRING([Filename], 1, charindex('.',[Filename])-1) as [New name] FROM [Table]
Human
  • 1
0

In MySQL, this work for me.

set @file = 'test1.test2.test3.docx';

SELECT      substring(@file, 1, (length(@file) - (length(substring_index(@file, '.', -1)) + 1))) as 'name_file',
            substring_index(@file, '.', -1) as 'extension';
Douglas Comim
  • 69
  • 1
  • 2