Remove extensions from filename

问题:

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

回答1:

try this one out:

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

View For a DEMO @ SQLFiddle.com

回答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])))

回答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

回答4:

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

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