CODEWITHSUNDEEP
sqlsql-servert-sql
user1144852
user1144852

Reputation: 265

Preserve VARBINARY value and store it to another variable as varchar

DECLARE @Blob VARBINARY(256) = CAST('SOMETHING' AS VARBINARY(256))
DECLARE @Var VARCHAR(256)

SELECT @Blob

Result of the above query is 0x534F4D455448494E47

SELECT @Var = CAST(@Blob AS varchar)
SELECT @Var

I want to save @Blob value in a parameter (@Var) as varchar but it just simply converts it back to 'SOMETHING'. Is there a work around for this?

Thanks

Upvotes: 0

Views: 101

Answers (1)

Joe Enos
Joe Enos

Reputation: 40431

Personally, I'd just store the bytes in the database, and convert to hex in the application on demand rather than storing the hex in the database. But...

You won't be able to do varchar(256), because if you have 256 bytes in hex, it will be double (512), plus the leading 0x if you want, for 514 total characters.

But if that's what you want, looks like Microsoft hasn't given you a built-in feature, but they do provide an example, found here:

create procedure sp_hexadecimal
  @binvalue varbinary(255)
as
declare @charvalue varchar(255)
declare @i int
declare @length int
declare @hexstring char(16)

select @charvalue = '0x'
select @i = 1
select @length = datalength(@binvalue)
select @hexstring = '0123456789abcdef'

while (@i <= @length)
begin

  declare @tempint int
  declare @firstint int
  declare @secondint int

  select @tempint = convert(int, substring(@binvalue,@i,1))
  select @firstint = floor(@tempint/16)
  select @secondint = @tempint - (@firstint*16)

  select @charvalue = @charvalue +
    substring(@hexstring, @firstint+1, 1) +
    substring(@hexstring, @secondint+1, 1)

  select @i = @i + 1

end

select 'sp_hexadecimal'=@charvalue

Upvotes: 2

Related Questions