Hex characters in varchar() is actually ascii. Need to decode it.

Question

This is such an edge-case of a question, I'd be surprised if there is an easy way to do this.

I have a MS SQL DB with a field of type varchar(255). It contains a hex string which is actually a Guid when you decode it using an ascii decoder. I know that sounds REALLY weird but here's an example:

The contents of the field: "38353334373838622D393030302D343732392D383436622D383161336634396339663931"

What it actually represents: "8534788b-9000-4729-846b-81a3f49c9f91"

I need a way to decode this, and just change the contents of the field to the actual guid it represents. I need to do this in T-SQL, I cannot use .Net (which if I could, that is remarkably simple).

UPDATE: Some people have responded with ways that may work in one-off statements, I need a way to put this into an UPDATE statement.

For example: UPDATE MyTable SET MyField = MyFunction(MyField)

Where MyFunction is the correct answer to this question.

Answer 1

this will give you what you want..8534788b-9000-4729-846b-81a3f49c9f91

select CONVERT(varchar(36),
0x38353334373838622D393030302D343732392D383436622D383161336634396339663931)

you need to convert the value to varbinary and then convert back to varchar

here is one way using dynamic SQL

    declare @v varchar(100)
 select @v = '0x' + '38353334373838622D393030302D343732392D383436622D383161336634396339663931'

exec ( 'SELECT CONVERT(varchar(36),' + @v + ')')

Answer 2

If you want to convert individual characters you can do it with the CHAR function as:

SELECT CHAR(0x38)

But you have to remember to prefix the number with 0x since it's hexadecimal.