Re: Convert to Hexidecimal

From: Steve Kass (skass_at_drew.edu)
Date: 05/19/04 

Date: Wed, 19 May 2004 09:20:25 -0400

Ben,

  Since you mentioned string functions, I'll guess that you want to
convert a numeric column to the string representation of its hexadecimal
value, in other words, you want, something that will convert the integer
271 to the string '1F'. Here's a function to do that for integers. You
can leave off the 0x prefix if you don't want it.

create function hexchar(
  @i int
) returns varchar(10)
as begin
  if @i < 0 return null
  declare @digits char(16)
  set @digits = '0123456789ABCDEF'
  declare @s varchar(10)
  set @s = cast(substring(@digits,@i%16+1,1) as char(1))
  set @i = @i/16
  while @i > 0 begin
    set @s = cast(substring(@digits,@i%16+1,1) as char(1))+@s
    set @i = @i/16
  end
  if len(@s)%2 = 1
    set @s = '0x0'+@s
  else
    set @s = '0x'+@s
  return @s
end
go

create table T (
  i int
)
insert into T values (10293)
insert into T values (293)
insert into T values (93)
go

create view T_hex as
select
  i,
  dbo.hexchar(i) as hex_i
from T
go

select * from T_hex
go

drop view T_hex
drop table T
drop function hexchar

If you want something else, please ask and give examples of the exact
input and output you want.

Ben Reese wrote:

>HI
>
>I need to convert a numeric column into a hexidecimal format in a view.
>
>I cannot see a TSQL Math or String Function that will do this (or an extended stored procedure perhapse)
>Is there a way to make the Convert fumnction do it? I can find nothing in Books Online or MSDN Library.
>
>Help please
>
>Ben
>
>

Relevant Pages

  • Re: Another Understanding Pointers Question
    ... > string functions to test. ... but I just wanted to test writing my own functions. ... > int main ... Sorry if thats a dumb question.. ...
    (comp.lang.c)
  • Re: What does const char *s mean? is s a constant or *s?
    ... > I've been looking at some code for string functions (certain ... See what the compiler tells you when you try to compile it: ... int ch; ...
    (comp.lang.c)
  • Re: No Decimal trailing sign
    ... You'll have to use string functions to do this. ... else ltrim(cast(-100*d as int)) + '-' end ... .txt file wants to see just the meaningful digits, no decimals, no zero fill ... see a trailing minus sign for negative numbers. ...
    (microsoft.public.sqlserver.mseq)