Converts LSN from hex to decimal and vice-versa

lsn-hex-dec-conversions.sql

-- convert Hex LSN to decimal (e.g. from fb_dblog() to backup sets)
-- credit https://stackoverflow.com/questions/70213942/convert-hex-current-lsn-to-decimal-converter
WITH s as (
    select Replace('00000016:000001a5:0001',':','.') lsn
)
select 
 Concat (
    Convert(bigint,Convert(varbinary,Concat('0x',ParseName(lsn,3)),1)) * 1000000,
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,2)),1)) * 10000,
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,1)),1))
) AS lsn
from s
GO

-- convert decimal LSN to Hex (e.g. from backup sets to fb_dblog())
-- WARNING: quick hack not sure it will always work
-- credit http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html
CREATE OR ALTER FUNCTION ConvertToBase  
(  
    @value AS BIGINT,  
    @base AS INT  
) RETURNS VARCHAR(MAX) AS BEGIN  
  
    -- some variables  
    DECLARE @characters CHAR(36),  
            @result VARCHAR(MAX);  
  
    -- the encoding string and the default result  
    SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',  
           @result = '';  
  
    -- make sure it's something we can encode.  you can't have  
    -- base 1, but if we extended the length of our @character  
    -- string, we could have greater than base 36  
    IF @value < 0 OR @base < 2 OR @base > 36 RETURN NULL;  
  
    -- until the value is completely converted, get the modulus  
    -- of the value and prepend it to the result string.  then  
    -- devide the value by the base and truncate the remainder  
    WHILE @value > 0  
        SELECT @result = SUBSTRING(@characters, @value % @base + 1, 1) + @result,  
               @value = @value / @base;  
  
    -- return our results  
    RETURN @result;  
  
END  
GO

with s as (
    select REVERSE('2200000042100001') lsn
)
SELECT
	RIGHT(REPLICATE('0', 8) + dbo.ConvertToBase(REVERSE(SUBSTRING(lsn, 15, 9)), 16), 8) + ':'
+	RIGHT(REPLICATE('0', 8) + dbo.ConvertToBase(REVERSE(SUBSTRING(lsn, 6, 9)), 16), 8) + ':'
+	RIGHT(REPLICATE('0', 4) + dbo.ConvertToBase(REVERSE(LEFT(lsn, 5)), 16), 4) AS lsn
from s