Convert hex (Current LSN) to decimal converter-CodePudding

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

CodePudding user response：

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