Convert HexString to Varbinary

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)

Convert HexString to Varbinary

Author

Topic

abarringer
Starting Member

13 Posts

Posted - 2006-03-14 : 11:23:46

I've read lots of articles on converting from hex strings to (var)binary .. but I need the opposite.

I have a CHAR(32) that looks like this F041200FA100FA... to 32 positions.

I need to convert it to a varbinary representation so that the varbinary would look like 0xF041200FA100FA....

Thanks for your help.

Kristen
Test

22859 Posts

Posted - 2006-03-14 : 12:03:07

If you had a "tally" table of numbers this would cover to an INT. You could probably adapt it to create a varbinary instead


DECLARE @hx		varchar(1000),
	@intMyInt	bigint,
	@intMyChar	int
SELECT	@hx = 'F041200FA100FA',
	@intMyInt = 0

SELECT	@intMyChar = ASCII(SUBSTRING(@hx, MyNumber, 1)),
	@intMyInt = (@intMyInt * 16) + 
		CASE WHEN @intMyChar <= ASCII('9') THEN @intMyChar - ASCII('0')
			WHEN @intMyChar <= ASCII('F') THEN @intMyChar - ASCII('A') + 10
			ELSE NULL
			END
FROM	dbo.MyNumbersList
WHERE	MyNumber <= LEN(@hx)
ORDER BY MyNumber
SELECT	[@intMyInt]=@intMyInt

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-14 : 12:57:49

If you don't need to go through integer to arrive at varbinary, have you considered this?

exec('select 0x' + 'F041200FA100FA')

Be One with the Optimizer
TG

abarringer
Starting Member

13 Posts

Posted - 2006-03-14 : 15:37:18

Maybe I should step back a little bit and ask a better question.

I have a text file with a bitmap that looks like this "AD9FFFEFFF7FDBF7FFFD0300000000". I need to import it with SSIS into a varbinary column.

I need to use the Lookup object in SSIS on a varbinary column. It will be using the OLE DB Destination for inserting into the table.

How do I do it?

My primary problem is that I can't find a datatype in the "Flat File Connection Manager" that works with the lookup column. I've tried everything obvious byte_stream(DT_BYTES), string(DT_STRING), text(DT_TEXT) etc. Whenver I try to map the column from the data from the "Flat File Connection Manager" to the table defined in the lookup component that is a varbinary it gives a datatype mismatch error specifically "One or more columns do not have supported datatypes, or their data types do not match".

The function needs to be very efficient we will be loading over hundreds of millions rows a day with it sic.

Any help would be greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources