Convert hex (Current LSN) to decimal converter

Question

how can we convert Log Sequence Number in SQL Server like this 000000dc:00003146:0002 in decimal value?

The LSN has three distinct parts https://stackoverflow.com/a/60519924/73226. Are you asking how `msdb.dbo.backupset` converts that to a single number? — Martin Smith, Dec 03 '21 at 12:34
I would like to get a result like 22000000042100001 which is 00000016:000001a5:0001 in decimal value — luca canonico, Dec 03 '21 at 13:58

Stu · Accepted Answer · 2021-12-03T15:01:08.320

Not 100% sure what you're expecting (an example would be useful) however the following shows how to convert each part to decimal and reconcatenate. If you are interested in each value separately then remove the concat_ws

with s as (
    select [Current LSN], Replace([Current LSN],':','.') lsn from sys.fn_dblog(null,null) 
)
select [Current LSN], 
Concat_Ws(':',
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,3)),1)),
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,2)),1)),
    Convert(int,Convert(varbinary,Concat('0x',ParseName(lsn,1)),1))
)
from s

I would like to get a result like 22000000042100001 which is 00000016:000001a5:0001 in decimal value

The value you are showing overflows a bigint however as a string representation you can tweak it slightly:

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))
)
from s

Result: 2200000042100001

I would like to get a result like 22000000042100001 which is 00000016:000001a5:0001 in decimal value — luca canonico, Dec 03 '21 at 13:49

Convert hex (Current LSN) to decimal converter

1 Answers1