SQL concatenate VARCHAR + VARBINARY with VARBINARY as hex 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
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