LOG Any Base

SQL Server 2012 now supports an optional integer base argument to the mathematical LOG function. Prior to SQL Server 2012 only the natural log and base-10 log were supported with the T/SQL native functions LOG and LOG10 respectively.

The function below provides the same functionality as SQL Server 2012 to earlier versions of SQL Server.

Thanks to Lea Verou who's post gave me the formula to use.

dbo.udf_Log

create function dbo.udf_Log(@argument float, @base int) returns float as
begin

    -- if @base = 1 it should throw Msg 3623, Level 16, State 1 - An invalid floating point operation occurred.

    declare @result float

    if (@base < 2)
        set @result = log(-1) -- just a way to throw the right error because we can't throw an actual error in a user defined function
    else
        set @result = log(@argument) / log(@base)

    return @result

end
go

RMJCS Syntax Highlighter

| Copyright 2007-2020 RMJCS