Convert binary value as string to binary

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 binary value as string to binary

Author

Topic

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-29 : 11:42:17

I have a binary value that's stored as a string (due to importing from excel) and I need to convert it to a binary value

ie
'0x000000002FDAF785'

Anyone have any ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 12:03:52

You can't convert to binary from string...why you don't you store in binary format directly when importing from excel?

Select convert(varbinary, 0x000000002FDAF785) -- this works
Select convert(varbinary, '0x000000002FDAF785') -- this won't

Harsh Athalye
India.
"Nothing is Impossible"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-29 : 12:07:57

Tried that, SQL sees the field as a string when importing from excel and fails.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-29 : 12:21:23

Sorry folk!!

I don't think there is any built-in function which can do that..

Harsh Athalye
India.
"Nothing is Impossible"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-29 : 13:36:39

Here's my solution....

1. Added a field to the table (binary datatype).
2. Created an update statement per line...
Select 'UPDATE table SET newfield = ' + fieldname + ' WHERE field = ''' + fieldname + '''' as sql
Into #MyTable
From table
3. sp_ExecResultSet (or our version of it)

May not be pretty, but it works....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-29 : 15:17:48

It's probably more efficient express the value directly using dynamic sql (like your solution) but here is a function to convert a string representation of a varbinary value to varbinary.

It uses one of MVJ's functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


if object_id('dbo.fn_strToVarBinary') > 0
	drop function dbo.fn_strToVarBinary
go

create function dbo.fn_strToVarBinary (@s varchar(8000))
returns varbinary(4000)
as
begin
	declare	@l int
		,@vb varbinary(4000)
	
	select	@l = len(@s)
		,@vb = 0x

	select	@vb = @vb+b
	from	(
		select	number
			,b = convert(binary(1),
				(convert(varbinary, charindex(substring(@s, number, 1), '0123456789ABCDEF')-1) * 16)
				+ isNull(nullif(charindex(substring(@s, number+1, 1), '0123456789ABCDEF')-1,-1),0)
			)
		from	dbo.F_TABLE_NUMBER_RANGE(1,@l)
		where	number%2 = 1
		) a
	order by number
	
	return substring(@vb,2,4000)
end

go

select	dbo.fn_strToVarBinary(sHex)
from	(
	select	'0x000000002FDAF785' sHex union all
	select '0x0A' union all
	select '0x160A' union all
	select '' union all
	select '0xA'
	--union all select replicate('FF', 3999) + 'AA'
	) a

output:
-------------------------
0x000000002FDAF785
0x0A
0x160A
0x
0xA0

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources