convert text string to hexadecimal representation or binary representation

Question

hello guys simple question that i seemed to be stumped on. I have something that seems to work but i get a blank result. I have a text string in a column in a database. for the sake of this question lets say the text string is "jhonSmith" I would like to get the hexadecimal representation of this string. using a sql server simple select statement. I do not have access to create or call procedures on the database. this is the best ive been able to come up with but it returns blank. If i take the convert function off then it works.

select distinct convert (varbinary, dim_employee.full_name) as Employee
from dim_employee
where 1=1

if i use varchar instead of varbinary i get the data back something leads me to believe im not doing it correctly. Can someone point me in the right direction. Please!!!

Answer 1

When I run the following

select distinct convert (varbinary, 'Hogan') as Employee

I get the following

Employee
--------------------------------------------------------------
0x486F67616E

Maybe there is a problem in the way you are displaying the results?