T-SQL Get File Extension Name from a Column
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
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
SELECT CASE
WHEN filepath LIKE '%.%' THEN RIGHT(filepath, Len(filepath) -
Charindex('.', filepath))
ELSE filepath
END FilePath
FROM tbl1
Demo