It would be fine to have this system function, and because we don’t this can be a replacement.
Note: additional checking can be added. You can also create your function for this. This is only logic.
This example shows how to get file extension.
DECLARE @Filename nvarchar(255),
@OnlyName nvarchar(255),
@Extension nvarchar(255),
@Pos int
SET @Filename = 'this.is.my.filename.with.dots.jpg'
SELECT @Pos = CASE (CHARINDEX('.', @Filename))
WHEN 0 THEN -1
ELSE LEN(@Filename) - CHARINDEX('.', REVERSE(@Filename)) +1
END
SET @Extension = SUBSTRING(@Filename, @Pos, LEN(@Filename))
SET @OnlyName = SUBSTRING(@Filename, 1, @Pos-1)
SELECT @Filename AS [Filename], @Pos AS Position,
@OnlyName AS OnlyName, @Extension AS Extension
