how to convert a varchar "hex" value 1

[Jasen]

The title probably makes little sense. I have a varchar that represents a hex value. I need to convert that to an int, then to it's character value. As in

CHAR(CONVERT(int,@myhexnumber))

How do I tell SQL that my varchar is already a varbinary, without converting or casting it? When I do either of those it really converts it into a hex value as if it was an int to start with. For instance if it's '15' or even '0x15', it converts to 0x3135 (or 0x30783135), but I want to convert the hex value '0x15' to integer. The convert function doesn't like it with the "0x" on the front, but is there another way to notate it maybe?

 

[vongrunt]

Hm... varbinary to hex is easy, in opposite direction - not without custom UDF... I think.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Jasen]

I figured, but not sure how to tackle this. My other post is probably confusing as well. In it's most simple terms:

DECLARE @string as varchar(2), @myBin as Binary(2)

SET @string = '52'

SET @myBin = ?
--I want @myBin to be 0x52

I'm not opposed to doing a UDF. Just not sure how to turn 52 into 0x52.

 

[gmmastros]

Just spitballing here...

Could you create a Lookup table to do your conversions. Of course, if your hex values are large, this may not work real well.

This is what I had in mind...

[green]-- Create the lookup table[/green]
Declare @T Table(N VarChar(1))
Insert Into @T
Select '0' Union All
Select '1' Union All
Select '2' Union All
Select '3' 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 'A' Union All
Select 'B' Union All
Select 'C' Union All
Select 'D' Union All
Select 'E' Union All
Select 'F'

Create 
Table 	HexToIntLookup
		(
		IntValue Integer Identity(0,1), 
		HexValue VarBinary, 
		VarCharValue VarChar(10), 
		AsciiValue VarChar(1)
		)

Insert Into HexToIntLookup(VarCharValue)
Select	VarCharValue
From	(
		Select '0x' + A.N + B.N As VarCharValue
		From   @T A, @T B
		) A
Order By VarCharValue

Update 	HexToIntLookup
Set		HexValue = IntValue

Update  HexToIntLookup
Set		AsciiValue = HexValue

Select * from HexToIntLookup

Then it should be easy to do lookups against this table.

Select *
From HexToIntLookup
Where VarCharValue = '0x41'



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Awright, let's do it in one line...

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[gmmastros]

vongrunt

Are you suggesting we optimize the creation of the lookup table (that has 256 records) so that it can all be done in 1 line?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Nope, I proposed some circus art exercise like this one:

declare @blah char(2); set @blah = '41'
select convert(varbinary, convert(uniqueidentifier, '00000000-0000-0000-0000-0000000000'+@blah)) % 256

Nah... too ugly.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[vongrunt]

OK, let's do it right:

create function base2int( @num varchar(16), @radix tinyint )
returns bigint
as
begin
	declare @ret bigint; set @ret = 0
	declare @chars char(16); set @chars = '01234567890ABCDEF'
	declare @base bigint; set @base = 1
	declare @i tinyint; set @i = len(@num)
	declare @pos tinyint

	while @i >= 1 and @ret is not null
	begin
		set @pos = charindex(substring(@num, @i, 1), @chars)
		if @pos = 0
			set @ret = null
		else
			set @ret = @ret + @base * (@pos-1)

		set @i = @i-1
		set @base = @base * @radix
	end

	return @ret
end
go

First parameter is varcharnumber, another is base (radix) used for conversion to int. Thing returns NULL in case of invalid varcharnumber.

select dbo.base2int('41', 16)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Jasen]

I was screwing around with the numbers, trying different things, and I think I may have found a formula that works, at least for numeric examples.
Check it...

declare @mys as varchar(2), @mybin as varbinary(1)

set @mys = '54'

set @mybin = cast((convert(int,left(@mys,1) + '0')/10*16) as varbinary) + convert(int,right(@mys,1))
select @mybin

From what I understand I'll be dealing only with 00-99 numbers, not any A-F's, so this might just work.
Look plausible?

 

[Jasen]

edit: didn't see your UDF there von grunt, I'm playing with that now too. So far, looks very interesting. thanks!

 

[vongrunt]

> Look plausible?

Yep. Or even:

select convert(varbinary(1), convert(int, @mys)%10 + convert(int, @mys)/10*16)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[JMCraig]

vongrunt's base2int function will work better with zero in the list of digits only once:

'0123456789ABCDEF'

rather than:

'01234567890ABCDEF'

As written, you'll find that you get somewhat unconventional results:

select dbo.base2int( '0A', 16 )

-------------------
11

So, if you use this code, you'll want to make that change.

John Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com