T-SQL Get File Extension Name from a Column

sql get filename without extension
how to get file path in sql server
read file name in sql server
sql find character from right
oracle get filename without extension
t sql get index of a character in a string
extract file name from path sql
t-sql find occurrence in string

I have a table with a column that contains the entire file name, the data looks like:

FilePath                       FileSize
------------------------------ --------
dsch2_chs_wyj.rar              694KB
AllInOneHash.rar               19KB
FilePropertyModifier.rar       12KB
jquery_1_7_api_chm_chs.rar     285KB
startupmgr.rar                 38KB
JQueryTableExample_Edi.rar     33KB
hdpartioncalc_csharp.rar       49KB
XMLMenuTest.zip                3KB

Now I want to extract the file extension name, like .rar and insert into a new table.

INSERT INTO Attachment
  (
    Id,
    [FileName],
    ExtensionName,
    StoredPath,
    CreateOn,
    UploaderIP,
    DataBinary
  )
SELECT ba.Id,
       ba.Title,
       '{Extension Name}',
       ba.FilePath,
       GETDATE(),
       NULL,
       NULL
FROM   BlogAttachment ba

But T-SQL doesn't have a LastIndexOf() function. How can I cut the substring in an easy way?

SELECT CASE 
         WHEN filepath LIKE '%.%' THEN RIGHT(filepath, Len(filepath) - 
                                                       Charindex('.', filepath)) 
         ELSE filepath 
       END FilePath 
FROM   tbl1 

Demo

Get extension of a file using SQL?, try this: declare @str varchar(20)='MyDoc.doc'; select reverse(left(reverse(@str),​charindex('.',reverse(@str))-1)). SQL fiddle demo. file_ID corresponds to the file_id column in the sys.master_files catalog view or the sys.database_files catalog view. Examples. This example returns the file names for file_ID 1 and file_ID in the AdventureWorks2012 database. SELECT FILE_NAME(1) AS 'File Name 1', FILE_NAME(2) AS 'File Name 2'; GO Here is the result set.

You can get the extension by doing:

 select reverse(left(reverse(FilePath), charindex('.', reverse(FilePath)) - 1))

However, I would recommend that you check that there is a '.' in the name first:

 select (case when FilePath like '%.%'
              then reverse(left(reverse(FilePath), charindex('.', reverse(FilePath)) - 1))
              else ''
         end) as Extension

Get only the file extension from a column with file names as strings , Get only the file extension from a column with file names as strings in MySQL? For this, use the substring_index() function. Insert some records in the table using insert command. Display all records from the table using select statement. Here is the query to get only the file extension in MySQL mysql> select substring_index(FileName,'.',-1) AS ALLFILENAMEEXTENSIONS from AllFiles; The following is the output

When looking for specific extensions, and dealing with texts that do have many dots, the following provides a more specific approach.

with extensionTable ([extension]) AS
(
    SELECT '.pdf'
    UNION ALL SELECT '.txt'
    UNION ALL SELECT '.doc'
    UNION ALL SELECT '.docx'
    UNION ALL SELECT '.xls'
    UNION ALL SELECT '.xlsx'
    UNION ALL SELECT '.ppt'
    UNION ALL SELECT '.pptx'
    UNION ALL SELECT '.zip'
    UNION ALL SELECT '.tar.gz'
    UNION ALL SELECT '.htm'
    UNION ALL SELECT '.html'
)

SELECT T2.[extension]
        , T1.[document_file_name]
FROM tbl T1
OUTER APPLY ( SELECT TOP 1 [extension] 
                FROM extensionTable
                WHERE CHARINDEX([extension], T1.[document_file_name]) > 0
                ORDER BY CHARINDEX([extension], T1.[document_file_name]) DESC, LEN([extension]) DESC
            ) T2 

Extract filename without extension from the absolute location , I'm trying to get a filename contained in the value of a specific column of my table. He suggested that if the filename is my_file.tar.gz then the return value I decided to look again at my own SQL and I came up with this (perhaps I tried lots of different permutations with this, but couldn't get it to work. Download Functions.zip - 678 B; Introduction. I store images on disk, and the path to the images in a database table - and for presentation reasons I needed to extract the filename without the extension in SQL, but realised it takes a little thinking about.

You can get file extension in this way:

select right('https://google.com/1.png', charindex('.', reverse('https://google.com/1.png') + '.') - 1)

And You can get file name in this way:

select right('https://google.com/1.png', charindex('/', reverse('https://google.com/1.png') + '/') - 1)

Extract filename from fully qualified path – SQLServerCentral, I have a varchar(255) column that contains the fully qualified path for a file. I need to break this data into the path, filename and extension. 'C:\Program Files​\Microsoft SQL Server\MSSQL\BACKUP\MyFile.txt'. select. Here is a T-SQL Query to list all the files in a folder. This uses a undocumented extended stored procedure to get the details. DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path DECLARE @FindFile TABLE (FileNames nvarchar(500) ,depth int ,isFile int) INSERT INTO @FindFile EXEC xp_DirTree @Path,1,1 SELECT FileNames from @FindFile where isFile=1

  declare @filepath char(250) = 'c:\powersql\database\teste.txtdat'
  Declare @NewExtesion Char(15) = 'Old' 



--  @filepath char(250)                           = 'c:\powersql\database\teste.txtdat'
--  select len(@FilePath)                         = 33
--  Select Charindex('.', @filepath)              = Len of Filepath before Extension  
--  select RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath) )   = txtdat (extension i would like to change)  
--  select REPLACE(@filepath, RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath)), 'xxx')
       -- here i changed .txdat to xxx

-- below the full query 

SELECT 
      CASE 
         WHEN @filepath LIKE '%.%' 
               THEN  REPLACE(@filepath, RIGHT(rtrim(@filepath) ,len(@FilePath) - Charindex('.', @filepath)), @NewExtesion)
         ELSE @filepath 
       END  

Parsing file names from paths using T-SQL - Andy M Mallon, So let's parse this stuff in T-SQL, even though other languages might be better for this. To get the file name, we'll apply the RIGHT function to the FilePath , and we can parse the file name into the file extension and a “bare” file name. filename, file in bytes, and date of last change – present in columns. 1. Name of the file . 2. Creation Date of the file. So let say if there are 10 files in a folder then i want a temp table which has 10 rows and two columns . Column 1 will contain the name of the file and column 2 will contain the creation date of the file. This task will need to be accomplished using T-sql only (not using SSIS).

get extension from a string [RESOLVED] ty, I am trying to allow searching of file extensions. I am passing in a string. However I am struggling as I cannot be sure if the extension is 3 or 4  Get Column Names From Table in SQL Server Example 1 In this SQL example, we will show you how to Get Column names using INFORMATION_SCHEMA. -- Query to Get Column Names From Table in SQL Server USE [SQL Tutorial] GO SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'NewCustomers'

Extracting the filename from a full path in SQL Server, I store images on disk, and the path to the images in a database table - and for presentation reasons I needed to extract the filename without the  What I'd like to do is pull that timestamp into the table as a field so I can concat it into a column of "Model_[timestamp]" and join it to an ever-growing master list of model results. Another thing I've tried is referencing the table at the "import CSV" step to get a list of the files, then pulling the timestamp out of the tilename.

Extract the Path from Filename in SQL, Reverse the full filename; Get the CharIndex of the first backslash ('\') Pathfromfullname('C:\Program Files\Microsoft SQL Server\MSSQL15. The files all follow the same naming convention where the year precedes the file name (ex. 2018_filename) . Is there a way to add a custom column to my orginial query that will display the file name? (Even better if there is a way for only the year of the file name to show!)

Comments
  • possible duplicate of Find index of last occurrence of a sub-string using T-SQL
  • new table... or new column?
  • @Joel Coehoorn sorry, i mean new colum in a new table, just as the code pasted above. The problem is solved. thanks anyway.
  • Possible duplicate of Remove extensions from filename
  • Doesn't handle files with multiple "." in name.
  • Wasn't part of the requirement.
  • this one doesn't handle all cases. Gordon Linoff answer works better for me
  • It's a tacit requirement.
  • @FreshPrinceOfSO . . . The original did work on my test example, but I fixed it.
  • @Abhishek . . . The second query works in that case, as explicitly stated in the answer.
  • This is not what was asked for. You should just extract extension. Even taking just the RIGHT(...) will not work always (because you cannot say for sure, that there is always one . in the path). This is why the question said "But T-SQL doesn't have a LastIndexOf() function." But you might have a try to correct your answer making use of the REVERSE() function.