Re: convert from hexadecimal to a decimal

From: Steve Kass (skass_at_drew.edu)
Date: 10/01/04 

Date: Fri, 01 Oct 2004 18:10:12 -0400

You mean this isn't self-documenting? ;)

  select
    '0x[89abcdef]' lower, '[0-9abcdef]' upper,
    0x100000000 parsename, '123456789ABCDEF' power,
    cast(0 as bigint) replicate, 16. stuff,
    7 charindex, 3 rtrim
  into ltrim

select
  b, sum(charindex(right(left(b,N),1),power)
     * power(stuff,len(b)-N))
    - case when lower(b) like lower+replicate(upper,charindex)
       then parsename else replicate end
from T, numbers, ltrim
where n between rtrim and len(b)
group by b, lower, upper, parsename, replicate, power, charindex
go

SK

Hugo Kornelis wrote:

>On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:
>
>
>
>>Very good, and how about this?
>>
>>create function hexchar2(
>> @b varchar(10)
>>) returns int
>>as begin
>> return (
>> select
>>
>>sum((charindex(right(left(@b,N),1),'123456789ABCDEF'))*POWER(16.,len(@b)-N))
>> from numbers
>> where n between 3 and len(@b))
>> - case when lower(@b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
>> then 0x0100000000 else cast(0 as bigint) end
>>end
>>
>>SK
>>
>>
>
>Hi Steve,
>
>Nice!
>
>With the added advantage that it can be used inline in the query, so that
>the overhad of calling a function is no longer incurred. (Though I would
>comment it if I used it in a query <g>)
>
>Best, Hugo
>
>

Relevant Pages

  • RE: how to delete space
    ... Ltrim and Rtrim are VBA functions. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Deleting empty spaces in a string
    ... then use the LTRIM() and RTRIM that. ... constraints, Declarative Referential Integrity, datatypes, etc. in your ... schema are. ...
    (microsoft.public.sqlserver.programming)
  • Re: Append Query Error
    ... Oops, I meant RTrim(), not LTrim. ... OfficeDev18 wrote: ... See the LTrim() function in the help file. ...
    (microsoft.public.access.queries)
  • Trimming of strings
    ... I need to trim strings in a select statement. ... I have used LTrim and RTrim ... white spaces. ...
    (microsoft.public.sqlserver.programming)