3

As the title suggests, is possible to concatenate the 2, with the result as VARCHAR and the VARBINARY data as hex?

DECLARE @test VARCHAR(50) = 'Hello World!' 
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + @testvb --I don't want to cast this back to var char

The result should be: Hello World!48656C6C6F20576F726C6421

0
3

this is what i wanted to do

DECLARE @test VARCHAR(50) = 'Hello World!' 
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2);
GO
|(No column name)                    |
|:-----------------------------------|
|Hello World!48656C6C6F20576F726C6421|

dbfiddle here

From this Aaron Bertrand blog post, I found out that CAST converts it back to original value whereas CONVERT has an option to keep the varbinary value:

SELECT @test + CAST(@testvb AS VARCHAR(MAX)) --converts back to Hello World!
SELECT @test + CONVERT(VARCHAR(MAX),@testvb,2) --keeps varbinary value
2
  • 4
    Hi Dave. Regarding "CAST converts it back to original value whereas CONVERT does not.": that is a mischaracterization of what Aaron wrote and of what is going on with those two functions. It's not that they behave differently, the difference is that CONVERT allows for applying a "style" number, which in your example is the 2 as the third parameter in the CONVERT function. But CONVERT without any "style" number works the same as CAST, which is also the same as CONVERT with a style of 0, because internally the CAST function is the CONVERT function with a style of 0. Mar 8 '17 at 14:59
  • As srutzky commented already, the "CAST converts it back to original value whereas CONVERT does not." part is just misunderstanding. Please edit/remove it. CAST and CONVERT do the same thing but the second is more flexible. Mar 8 '17 at 16:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.