Get extension of a file using SQL? [duplicate]

问题:

Possible Duplicate:
Get substring in SQL Server

Let's say I have MyImage.png or MyDoc.doc, etc in a column in a database table.

How can I only get the file extension?

回答1:

try this:

declare @str varchar(20)='MyDoc.doc';
select reverse(left(reverse(@str),charindex('.',reverse(@str))-1))

SQL fiddle demo

回答2:

Try this :

select parsename(filename,1) from yourTable

sqlfiddle demo

回答3:

With string functions:

SELECT Extension =
   Right([Name],
        CHARINDEX('.',
                  REVERSE([Name]))-1)
FROM dbo.Files

SQL fiddle demo

回答4:

declare @str varchar(20)='MyDoc.doc'
select reverse(left(reverse(@str),CHARINDEX('.',reverse(@str))-1))

FIDDLE DEMO

回答5:

  SELECT RIGHT('myFile.txt', 
               CHARINDEX('.', REVERSE('myFile.txt'))-1) AS 'File Extension'

Documentation: SUBSTRING, RIGHT, CHARINDEX, REVERSE