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