August 31, 2016 at 1:16 pm
I am trying to convert this code below to MSSQL (from Oracle). I have tried several bad attempts but the TO_NUMBER / TO_CHAR is frustrating me. any one that can help I would REALLY appreciate it. I could not find any links about ms sql doing this, plenty of oracle. needs to be something out there for MS SQL!!!
Convert from DEC to HEX
LPAD(trim(TO_CHAR(substr(serial_number_dec,0,10), 'XXXXXXXX')),8,'0')
|| LPAD(trim(TO_CHAR(substr(serial_number_dec,11,8), 'XXXXXX')),6,'0')
Convert from HEX to DEC
LPAD(TO_NUMBER(substr(serial_number_hex,1,8), 'XXXXXXXXXX'),10,'0')
|| LPAD(TO_NUMBER(substr(serial_number_hex,9,6), 'XXXXXXXX'),8,'0')
August 31, 2016 at 1:57 pm
There are several different ways. The simplest one is probably something like:
--Convert Int to Hex:
SELECT CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), 345678),2)
--Convert Hex to Int:
SELECT CONVERT(INT,CONVERT(VARBINARY(4),'0005464E',2))
You can find others, likely some better approaches - try searching on: SQL Server Hex to Decimal
Sue
September 1, 2016 at 9:05 am
this was my solution
RIGHT('0000000000' + CONVERT(VARCHAR,CONVERT(BIGINT, CONVERT(VARBINARY(4), LEFT(serial_number_dec,8)), 2)),10) +
RIGHT('00000000' + CONVERT(VARCHAR,CONVERT(BIGINT, CONVERT(VARBINARY(4), SUBSTRING(serial_number_dec,9,6), 2)),8),8)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply