Re: convert from hexadecimal to a decimal

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

  • Next message: Hugo Kornelis: "Re: convert from hexadecimal to a decimal" 
    Date: Fri, 01 Oct 2004 03:35:26 -0400

    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

    Hugo Kornelis wrote:

    >On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:
    >
    >
    >
    >>This is not too efficient, but it should do the trick.
    >>
    >>
    >
    >Hi Steve,
    >
    >How about using a numbers table to speed it up?
    >
    >create function hexchar2(
    > @b varchar(10)
    >) returns int
    >as begin
    > declare @n bigint
    > set @b = substring(@b,3,8)
    > set @n = (select
    >sum((charindex(left(right(@b,n),1),'0123456789ABCDEF')-1)*POWER(16,(n-1)))
    > from dbo.numbers
    > where n between 1 and len(@b))
    > return
    > case when @n >= 0X80000000
    > then @n - 0x0100000000
    > else @n end
    >end
    >go
    >
    >
    >Best, Hugo
    >
    >

  • Next message: Hugo Kornelis: "Re: convert from hexadecimal to a decimal"

    Relevant Pages

    • Re: convert from hexadecimal to a decimal
      ... create function hexchar2( ... returns int ... declare @n bigint ...
      (microsoft.public.sqlserver.mseq)
    • Re: Triiger -vs- Constraint
      ... >Hugo Kornelis writes: ... but you still can't use it in a FOREIGH KEY constraint. ... > CREATE TABLE c (c int NOT NULL PRIMARY KEY) ... I can't wait until SQL Server 2005 is released, ...
      (comp.databases.ms-sqlserver)
    • Re: convert from hexadecimal to a decimal
      ... a UDF call, you can use something more like this: ... create function hexchar2( ... Hugo Kornelis wrote: ... >query to reflect the names of your numbers table and column. ...
      (microsoft.public.sqlserver.mseq)
    • Re: convert from hexadecimal to a decimal
      ... >create function hexchar2( ... >) returns int ... Hi Steve, ... comment it if I used it in a query ) ...
      (microsoft.public.sqlserver.mseq)
    • Re: conversion problem
      ... Both @i and @j are int ... "Hugo Kornelis" wrote: ... >>I have a problem converting nvarchar into numeric ... >>I've checked the Substring returns only digits ...
      (microsoft.public.sqlserver.programming)