How to convert Oracle TO_NUMBER to SQL Server-CodePudding

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

CodePudding user response：

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

select  CONVERT(INT, 0x000000000000000000001111)

outputs

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

CodePudding user response：

This should do the work.

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