SQL Hex - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Hex

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2005-11-30 : 10:35:19

I have a field that holds Hex values from A00-DFF, I need to be able to find all the values that are missing, is this possible? the field type is varchar and i have tried to convert it to binary, varbinary, int... and i cant seem to be able to get it to find the missing values.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-30 : 11:08:39

I would convert the hex values to int first, then use one of the methods in this topic to find the missing integers
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58310


--convert hex to int
declare @intLow int
	,@intHi int
select	@intLow = convert(int, 0xA00)
	,@intHi = convert(int, 0xDFF)

select @intLow [i], 0xA00 [h]
union
select @intHi [i], 0xDFF [h]

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-30 : 11:20:50

To convert the integers back to a string representation of a hex (or varbinary), you can use the function below like this: (if you use the function, I'd suggest using an existing "numbers" table rather than a dynamic one)

select dbo.fnVarBinaryToHexStr(convert(varbinary,IntColumn))
from myMissingInts


If object_id('dbo.fnVarBinaryToHexStr') > 0
	drop function dbo.fnVarBinaryToHexStr
go

create function dbo.fnVarBinaryToHexStr (@vb varbinary(1024)) returns varchar(2050) as 
begin
	declare @hex varchar(2050)
	
	------------------------------------------------------------------
	--create a table of numbers (1-1024; handles varbinary(1024) varchar(2048) 
	declare @position table (pos smallint)
	insert @position
	select	p1+p2+p3+p4+p5+p6+p7+p8+p9+p10+1 pos
	from
		(select 0 p1 union select 1) pos1
	cross join (select 0 p2 union select 2) pos2
	cross join (select 0 p3 union select 4) pos3
	cross join (select 0 p4 union select 8) pos4
	cross join (select 0 p5 union select 16) pos5
	cross join (select 0 p6 union select 32) pos6
	cross join (select 0 p7 union select 64) pos7 
	cross join (select 0 p8 union select 128) pos8
	cross join (select 0 p9 union select 256) pos9  
	cross join (select 0 p10 union select 512) pos10
	------------------------------------------------------------------

	--Assign @hex value
	select	@hex = coalesce(@hex + hexByte, '0x' + hexByte)
	from	(
		select	hexByte = 
			substring(hb, (convert(int,substring(vb, pos, 1)) / 16)+1, 1)
			+ substring(hb, (convert(int,substring(vb, pos, 1)) % 16)+1, 1)
			,pos
		from	(select @vb vb) as varbin
		cross join (select '0123456789ABCDEF' hb) as hexbank
		cross join @position as position
		where	pos <= len(vb)
		) h
	order by pos

	return @hex
end

go

--test this puppy out
select  dbo.fnVarBinaryToHexStr(0x123456789abcdef1234)
--output: 0x0123456789ABCDEF1234

Be One with the Optimizer
TG

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2005-11-30 : 11:26:04

im still stuck, i do not know how to fill the temp table with all available hex values

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-30 : 11:29:16

>>im still stuck, i do not know how to fill the temp table with all available hex values

well, give us something to work off of. See this link for what we need to help you:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2005-11-30 : 11:44:32

Table: Sites
------------
Ring VarChar(3)

Values
------------
A00
A01
A05
A06
A07
A08
A09
A0A
...

What i would like for a return would be

A02
A03
A04
A0B
...

Have I explained it well enough?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-30 : 17:03:27

Yeah, that was a little tricky. Here is code to convert your 3 character hex strings to integers:


-----------------------------------------------------
--you can run this code as is:

set nocount on
--DDL
create table Sites (Ring varchar(3))
go
insert Sites (Ring)
select 'A00' union all
select 'A01' union all
select 'A05' union all
select 'A06' union all
select 'A07' union all
select 'A08' union all
select 'A09' union all
select 'A0A'
go

--convert hex to int (this only works for 3 place hex code)
select	ring as hRing
	,sum(power(16,n-1) *
		case 
		when isNumeric(c)=1 then convert(int,c)
		else ascii(upper(c))-55
		end
	) as [iRing]
into	UsedRing
from	(
	select	ring
		,substring(reverse(ring), n, 1) c
		,n
	from	sites
	cross join (select 1 n union select 2 union select 3) n
	) a
group by ring

--display the hex values converted to int
select * from UsedRing
go
drop table sites
drop table usedRing
-----------------------------------------------------

next you can use MVJ's process to find missing numbers within a range:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58310

Finally use the function I posted (above) dbo.fnVarBinaryToHexStr to change your available integers to a 3 character string representation of hex.


select right(dbo.fnVarBinaryToHexStr(convert(varbinary,<IntColumn>)),3) from <AvailableIntsTable>

ok HalaszJ, all the hard stuff is done. You'll have to put all the pieces together yourself

Be One with the Optimizer
TG

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2005-12-01 : 09:31:55

Wow TG, you have mad skills, thanks once again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources