I have recently issued nearly 2000 ID mifare cards to all the new students who recently started at the college at which I work. Accidentally, the software that printed the cards reversed the LSB of the UID of each card which means I have a useless database of codes. I have tried to write some SQL to run against the mifare codes but my biggest obstacle is the conversion to the correct data types. Here is an actual example of the conversion I would be very grateful to receive.

Using only SQL, I need to convert 437910961 to 2986154266. To do this first convert 437910961 to Hex, which is 1A19FDB1. Then reverse this to B1FD191A. Converting this back to decimal gives the desired answer of 2986154266. However, both the initial number and the result are stored a varchar as apposed to bigint.

If you fancy the challenge then good luck, as this is driving me bonkers!

Thanks – Desperate!

9 Years
Discussion Span
Last Post by dickersonka
DECLARE @inid bigint
declare @outid bigint
DECLARE @hexstring VARCHAR(max)

--set your first value here
set @inid = 437910961
select @hexstring = master.dbo.fn_varbintohexstr(@inid)
select @hexstring = reverse(@hexstring)
select @hexstring = substring(@hexstring, 0, (len(@hexstring) - 9))
select @hexstring = '0x' + @hexstring
declare @binvalue varbinary(max), @hexstring1 varchar(max), @sqlstring nvarchar(max)
SET @sqlstring = 'SELECT @binvalue = ' +  @hexstring
EXEC sp_executesql @sqlstring, N'@binvalue VARBINARY(max) OUTPUT', @binvalue OUTPUT
select @binvalue
select master.dbo.fn_varbintohexstr(@binvalue)
SELECT convert(bigint, @binvalue)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.