Re: Converting hex number to integer

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Steve Kass (skass_at_drew.edu)
Date: 03/30/04 

Date: Tue, 30 Mar 2004 08:57:50 -0500

Francois,

  I don't know what message you are referring to - the first message in
this thread didn't mention the version of SQL Server you are using.

In any case, you can leave out the collate clause if your server is
case-insensitive, and you can try replacing cast(... as bigint) with
convert(decimal(10), ...). I don't know exactly which features don't
exist in 6.5.

SK

Francois Piette wrote:

>My SQL version (6.5) doesn't understand "collate" nor cast.
>Have you seen my message with the actual SQL request ?
>
>
>--
>francois.piette@overbyte.be
>Author of ICS (Internet Component Suite, freeware)
>Author of MidWare (Multi-tier framework, freeware)
>http://www.overbyte.be
>
>
>"Steve Kass" <skass@drew.edu> a écrit dans le message de
>news:efZAFkiFEHA.2408@TK2MSFTNGP10.phx.gbl...
>
>
>> Here is a somewhat obscure solution that works for up to 8 hex digits
>>
>>
>and
>
>
>>assumes 2's complement representation. There are many ways of doing this,
>>and if you search groups.google.com for sqlserver+hex+string+convert I
>>
>>
>think
>
>
>>you'll find some others.
>>
>>create table T (
>> h varchar(10)
>>)
>>insert into T values ('0X34fd1022')
>>insert into T values ('0X5000')
>>insert into T values ('0Xffff')
>>insert into T values ('0X80000000')
>>go
>>
>>
>>select h,
>> - len(h)/10*patindex('%[89ABCDEF]%' collate Latin1_General_CI_AS
>>,substring(h,2,2))/2 * power(2.,32) +
>> cast(sum(charindex(substring(h,N,1),'123456789ABCDEF' collate
>>Latin1_General_CI_AS) * power(16.,len(h)-N))
>> as bigint) as n
>>from T, (
>> select 3 as N union all select 4 union all select 5 union all
>> select 6 union all select 7 union all select 8 union all
>> select 9 union all select 10
>>) N
>>where N <= len(h)
>>group by h
>>
>>go
>>
>>drop table T
>>
>>-- Steve Kass
>>-- Drew University
>>-- Ref: 65F00CE1-CC26-4E04-8690-87A81AC1DA20
>>
>>Francois Piette wrote:
>>
>>
>>
>>>I would like to convert a string representing an hex number to an
>>>integer.Unfortunately the CONVERT function doesn't like CHAR or VARCHAR.
>>>
>>>SELECT CONVERT(INT, 0x1AB2) works very well but
>>>SELECT CONVERT(INT, "0x1AB2") gives an error message.
>>>
>>>btw: In my real problem, "0x1AB2" comes from a SUBSTRING applied to a
>>>
>>>
>column
>
>
>>>value.
>>>
>>>Any idea ?
>>>
>>>--
>>>francois.piette@overbyte.be
>>>Author of ICS (Internet Component Suite, freeware)
>>>Author of MidWare (Multi-tier framework, freeware)
>>>http://www.overbyte.be
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>

Relevant Pages

  • Re: Adding document properties for RTF files
    ... Author of ICS (Internet Component Suite, freeware) ... > and then have this filter call the rtf ifilter. ... > Looking for a SQL Server replication book? ... >> Author of ICS (Internet Component Suite, ...
    (microsoft.public.inetserver.indexserver)
  • Re: collation for multiple language in a column
    ... The query returns 100 rows, and without the collate clause, it would have ... COLLATE clause, SQL Server needs to add a Sort operator to the plan. ... is such that the TOP 100 restriction makes it very easy for the optimizer ...
    (microsoft.public.sqlserver.server)
  • Re: Microsoft Access and SQL Server
    ... to BigInt in the SQL Server database. ... When I do a query against SQL Server using Access - a select or make table ... what to do with a BigInt data type. ...
    (microsoft.public.access.queries)
  • Re: Maximum number of bits
    ... CAST(1 AS BIGINT) AS pwr ... INNER JOIN Islands AS n ... "Expert SQL Server 2008 Encryption" ... "Michael Coles" wrote in message ...
    (microsoft.public.sqlserver.programming)
  • Re: What did I do wrong?
    ... between the two tables via a FOREIGN KEY constraint. ... Columnist, SQL Server Professional ... When I changed the columns to INT from BIGINT, ...
    (microsoft.public.sqlserver.server)