converting Oracle SQL used to convert HEX MSID to ESN to MS SQL

• ghughes

  SSCrazy

  Points: 2201

  More actions

  August 31, 2016 at 1:16 pm

  #316850

  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')

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 31, 2016 at 1:57 pm

  #1898649

  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

• ghughes

  SSCrazy

  Points: 2201

  More actions

  September 1, 2016 at 9:05 am

  #1898798

  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)