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

Posted by csauve on Stack Overflow See other posts from Stack Overflow or by csauve
Published on 2010-05-25T19:42:28Z Indexed on 2010/05/25 20:01 UTC
Read the original article Hit count: 155

Filed under:

sql

|

encoding

|

ascii

|

hex

|

decoding

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).

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about encoding

<?xml version=“1.0” encoding=“UTF-8”?> not <?xml version='1.0' encoding='UTF-8'?>

as seen on Stack Overflow - Search for 'Stack Overflow'
I am using lxml with tree.write(xmlFileOut, pretty_print = True, xml_declaration = True, encoding='UTF-8' to write out my opened and edited xml file, but I absolutely need to have the xml declaration as <?xml version=“1.0” encoding=“UTF-8”?> and NOT <?xml version='1.0' encoding='UTF-8'… >>> More
Ivar definitions show 'long' type encoding as 'long long' type encoding

as seen on Stack Overflow - Search for 'Stack Overflow'
I've found what I think may be a bug with Ivar and Objective-C runtime. I'm using XCode 3.2.1 and associated libraries, developing a 64 bit app on X86_64 (MacBook Pro). Where I would expect the type encoding for the following "longVal" to be 'l', the Ivar encoding is showing a 'q' (which is a 'long… >>> More
How to avoid encoding the key of request parameters being encoding

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm trying to send a http request using WS.url() with a action receive a custom class parameter like public static void add(@Valid MyPage info) {...} There is a Map in MyPage @Required public Map<String, String> content = new HashMap<String, String>(); But When I try to send a request… >>> More
C# Check if character exists in encoding

as seen on Stack Overflow - Search for 'Stack Overflow'
I am writing a program that a part renders a bitmap font in CP437. In a function that renders the text with I want to be able to check whether a char is available in CP437 before the encoding conversion, like: public static void DrawCharacter(this Graphics g, char c) { if (char_exist_in_encoding(Encoding… >>> More
How to detect the character encoding of a text file?

as seen on Stack Overflow - Search for 'Stack Overflow'
I try to detect which character encoding is used in my file. I try with this code to get the standard encoding public static Encoding GetFileEncoding(string srcFile) { // *** Use Default of Encoding.Default (Ansi CodePage) Encoding enc = Encoding.Default; // *** Detect byte… >>> More