varchar（）中的十六进制字符实际上是ascii。需要解码它 [英] Hex characters in varchar() is actually ascii. Need to decode it

问题描述

我有一个MS SQL DB，这是一个问题的边缘案例，如果有一个简单的方法可以做到这一点，我会感到惊讶。与类型varchar（255）的字段。它包含一个十六进制字符串，当你使用ascii解码器对它进行解码时，它实际上是一个Guid。我知道这听起来真的很奇怪，但这里有一个例子：

该字段的内容：38353334373838622D393030302D343732392D383436622D383161336634396339663931

它实际上代表了什么：8534788b-9000-4729-846b-81a3f49c9f91

我需要一种解码方法，只需将字段的内容更改为实际它代表的guid。我需要在T-SQL中这样做，我不能使用.Net（如果可以的话，这非常简单）。

$ b

UPDATE ：有些人已经用可能在一次性语句中工作的方式做出了回应，我需要一种将它放入UPDATE语句的方法。

例如：UPDATE MyTable SET MyField = MyFunction（MyField）

其中MyFunction是此问题的正确答案。 这个会给你你想要的东西.8534788b-9000-4729-846b-81a3f49c9f91

  select CONVERT（ varchar（36），
 0x38353334373838622D393030302D343732392D383436622D383161336634396339663931）
  

您需要将该值转换为varbinary，然后转换回varchar

这里是使用动态SQL的一种方式

  declare @v varchar（100）
 select @v ='0x'+'38353334373838622D393030302D343732392D383436622D383161336634396339663931' 
 $ b $ exec（'SELECT CONVERT（varchar（36），'+ @v +'）'）
  

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.

解决方案

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 + ')')

这篇关于varchar（）中的十六进制字符实际上是ascii。需要解码它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！