MS SQL 服务器 - 将十六进制字符串转换为整数 [英] MS SQL server - convert HEX string to integer
问题描述
这个看似相同问题的答案:
This answer to what looks like the same question:
..对我不起作用.
我无法使用 MS SQL Server 2005 CAST 或 CONVERT 将十六进制字符串转换为整数.我错过了一些微不足道的东西吗?我进行了广泛的搜索，我能找到的最好的是冗长的用户函数，将十六进制字符串值转换为看起来像十进制整数的东西.肯定有一种简单的方法可以使用内置函数直接在查询中执行此操作，而不是编写用户函数?
I am not able to go to a HEX string to an integer using MS SQL server 2005 CAST or CONVERT. Am I missing something trivial? I have searched extensively, and the best I can find are long-winded user functions to go from a hex string value to something that looks like a decimal int. Surely there is a simple way to do this directly in a query using built in functions rather than writing a user function?
谢谢
编辑以包含示例:
选择 CONVERT(INT, 0x89)
select CONVERT(INT, 0x89)
按预期工作，但是
从某个表中选择 CONVERT(INT, '0x' + substring(msg, 66, 2))
select CONVERT(INT, '0x' + substring(msg, 66, 2)) from sometable
让我:
将 varchar 值 '0x89' 转换为数据类型 int 时转换失败."
"Conversion failed when converting the varchar value '0x89' to data type int."
额外的显式 CAST:
an extra explicit CAST:
选择 CONVERT(INT, CAST('0x89' AS VARBINARY))
select CONVERT(INT, CAST('0x89' AS VARBINARY))
执行，但返回 813185081.
executes, but returns 813185081.
将Int"、Decimal"等替换为Varbinary"会导致错误.一般来说，如果需要，看起来是数字的字符串会被解释为数字，但在这种情况下不是这样，并且似乎没有识别 HEX 的 CAST.我想认为有一些简单而明显的东西，但我刚刚错过了.
Substituting 'Int', 'Decimal', etc for 'Varbinary' results in an error. In general, strings that appear to be numeric are interpreted as numeric if required, but not in this case, and there does not appear to be a CAST that recognizes HEX. I would like to think there is something simple and obvious and I've just missed it.
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) 2009 年 9 月 6 日 01:43:32 版权所有 (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1(Build 2600:Service Pack 3)
Microsoft SQL Server 2005 - 9.00.3080.00 (Intel X86) Sep 6 2009 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)
总结:我想取一个十六进制字符串，它是表中的一个值，并将它作为查询结果的一部分显示为十进制整数，仅使用系统定义的函数，而不是 UDF.
To sum up: I want to take a hex string which is a value in a table, and display it as part of a query result as a decimal integer, using only system defined functions, not a UDF.
推荐答案
感谢您提供一些更明确的示例.据我从文档和谷歌搜索中得知，如果没有 UDF 或其他程序代码，这在 MSSQL 2005 中是不可能的.在 MSSQL 2008 中，CONVERT() 函数的 style 参数现在支持二进制数据，因此您可以直接这样做:
Thanks for giving some more explicit examples. As far as I can tell from the documentation and Googling, this is not possible in MSSQL 2005 without a UDF or other procedural code. In MSSQL 2008 the CONVERT() function's style parameter now supoprts binary data, so you can do it directly like this:
select convert(int, convert(varbinary, '0x89', 1))
在以前的版本中，您的选择是:
In previous versions, your choices are:
- 使用 UDF(TSQL 或 CLR；CLR 实际上可能更容易)
- 将 SELECT 包装在一个存储过程中(但无论如何你可能仍然有一个 UDF 等价物)
- 在应用前端进行转换
- 升级到 MSSQL 2008
如果转换数据仅用于显示目的，应用程序可能是最简单的解决方案:无论如何，数据格式通常都属于那里.如果您必须在查询中执行此操作，那么 UDF 是最简单的，但性能可能不是很好(我知道您说过您不想使用 UDF，但不清楚为什么).我猜想仅仅为此升级到 MSSQL 2008 可能不太现实.
If converting the data is only for display purposes, the application might be the easiest solution: data formatting usually belongs there anyway. If you must do it in a query, then a UDF is easiest but the performance may not be great (I know you said you preferred not to use a UDF but it's not clear why). I'm guessing that upgrading to MSSQL 2008 just for this probably isn't realistic.
最后，仅供参考，您包含的版本号是 Management Studio 的版本，而不是服务器的版本号.为此，请使用 select @@version 或 select serverproperty('ProductVersion') 查询服务器本身.
Finally, FYI the version number you included is the version of Management Studio, not the version number of your server. To get that, query the server itself with select @@version or select serverproperty('ProductVersion').
这篇关于MS SQL 服务器 - 将十六进制字符串转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
