How to convert Oracle TO_NUMBER to SQL Server

user_5333678 0
Viewed 520

I am migrating some SQL scripts from Oracle to SQL Server and have come across an issue when trying to convert Oracles TO_NUMBER into T-SQL code. Looked through the web for some time but never found the answer but they all say to convert hex using CAST or CONVERT with VARBINARY.

An example of the issue I am getting is below.

In Oracle:

select
    to_number( '000000000000000000001111', 'XXXXXXXXXXXXXXXXXXXXXXXX' )
from
    dual

returns 4369

When trying to use T-SQL CONVERT:

select
    CONVERT(VARBINARY, '000000000000000000001111')

returns 0x303030303030303030303030303030303030303031313131

and

select
    CONVERT(INT, CONVERT(VARBINARY, '000000000000000000001111'))

returns 825307441

Any help would be greatly appreciated.

Thanks

oracle sql-server
2 Answers

TSQL has binary literals, so you can write it like this:

select  CONVERT(INT, 0x000000000000000000001111)

outputs

4369

If you are starting with a hex string, you can use convert with the binary style of 1, or 2, so

 select convert(int, convert(varbinary(1024),'000000000000000000001111', 2))

outputs

4369
user_72977000

This should do the work.

select convert(bigint, convert(Varbinary(MAX), '000000000000000000001111',2))
user_52836630
Related