Skip to content

Instantly share code, notes, and snippets.

@bitwalker
Created April 16, 2014 20:43
Show Gist options
  • Save bitwalker/10931039 to your computer and use it in GitHub Desktop.
Save bitwalker/10931039 to your computer and use it in GitHub Desktop.
Convert a hex-encoded string to varbinary - MSSQL
declare @hexstring varchar(max)
declare @bin varbinary(max)
set @hexstring = '0x10.....'
set @bin = (
select cast('' as xml).value('xs:hexBinary(substring(sql:variable("@hexstring"), sql:column("t.pos")))', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
)
select @bin
update db.dbo.table
set column = @bin
where id = 1
@CharlesKai
Copy link

thanks a lot, built-in function of mssql has input length limit, such as convert and fn_cdc_hexstrtobin

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment