Convert string decimal to HEX, reverse, then back

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Convert string decimal to HEX, reverse, then back

Author

Topic

tstevens01
Starting Member

3 Posts

Posted - 2008-09-14 : 16:51:54

Convert string decimal to HEX, reverse, then convert back to string decimal.

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!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-14 : 21:38:40

This seems to do what you want.


select	Result=
	convert(bigint,convert(varbinary(8),reverse(convert(varbinary(8),437910961))))


Result               
-------------------- 
2986154266

(1 row(s) affected)

CODO ERGO SUM

tstevens01
Starting Member

3 Posts

Posted - 2008-09-15 : 03:01:07

Thanks Michael although the converstion to and from the number strings I am still struggling with.

Using your code, I need to do something like this:-

SELECT convert(varchar(10),convert(bigint,convert(varbinary(8),reverse(convert(varbinary(8),'437910961')))))

...noting that the number starts as a string and finishes as a string.

Any further ideas, as the above does not work?

Regards

Terry (rookie)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 03:35:08

It seems like a problem for "little endian" to "big endian" conversion...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 03:59:27

[code]CREATE FUNCTION dbo.fnShiftEndian
(
@Source BIGINT
)
RETURNS BIGINT
AS
BEGIN
RETURN CASE
WHEN @Source < 0 OR @Source > 4294967295 THEN NULL
ELSE (@Source & 0xFF) * 16777216
+ (@Source & 0xFF00) * 256
+ (@Source & 0xFF0000) / 256
+ (@Source & 0xFF000000) / 0x1000000
END
END[/code]

SELECT dbo.fnShiftEndian('437910961')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 04:14:47

quote:
Originally posted by tstevens01

SELECT convert(varchar(10),convert(bigint,convert(varbinary(8),reverse(convert(varbinary(8),'437910961')))))

SELECT convert(varchar(100),convert(bigint,convert(varbinary(8),reverse(convert(varbinary(8),cast('437910961' as int))))))

E 12°55'05.63"
N 56°04'39.26"

tstevens01
Starting Member

3 Posts

Posted - 2008-09-15 : 04:58:18

Peso

Your 'fnShiftEndian' function has worked perfectly!! Thanks. I now have to understand how it works!!!!

Unfortunately, your second posting worked for some entries although some 'strings' in my database exceeded the maximum for an integer.

Thanks very much, I can now sleep at night!

Tack själv

Terry (still a rookie, but a happy one)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 05:22:37

[code]DECLARE @Source VARCHAR(20)

SET @Source = '12884901885' -- too much for an INT

SELECT @Source,
CAST(CAST(REVERSE(SUBSTRING(CONVERT(VARBINARY(8), CAST(@Source AS BIGINT)), PATINDEX('%[^0]%', CAST(CONVERT(VARBINARY(8), CAST(@Source AS BIGINT)) AS VARCHAR(20))), 8)) AS VARBINARY(8)) AS BIGINT)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources