SQL Server - Change filename extension

CREATE FUNCTION change_ext(@filename nvarchar(MAX), @ext nvarchar(10))
RETURNS nvarchar(MAX)
AS
BEGIN
    RETURN LEFT(@filename, LEN(@filename) - CHARINDEX('.', REVERSE(@filename))) + @ext
END;
> DECLARE @str NVARCHAR(max) = 'C:\Windows\system32\Windows.System.Profile.PlatformDiagnosticsAndUsageDataSettings.dll'
> SELECT dbo.change_ext(@str, '.txt')

C:\Windows\system32\Windows.System.Profile.PlatformDiagnosticsAndUsageDataSettings.txt
值得注意的是,经过测试,在SQL SERVER 2016环境下,大量的自定义函数调用效率相比直接调用built-in函数要低不少。