SQL 如何使用特定值更新 varbinary 字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8567222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update a varbinary field with a specific value?
提问by shicky
Basically I am trying to give a user a certain password so I can test some functionality on a system, as I only have our admin account and I can't play with that I am just picking a random account so I can do my testing. So here is my attempt at an update:
基本上，我试图给用户一个特定的密码，以便我可以在系统上测试某些功能，因为我只有我们的管理员帐户，我不能玩，我只是选择一个随机帐户，以便我可以进行测试。所以这是我的更新尝试：
UPDATE dbo.Login
SET
Salt=CAST('bPftidzyAQik' AS VARBINARY),
Password=CAST('0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B' AS VARBINARY)
WHERE LoginID=10947
It runs fine however the code in the database looks japanese for one and the syntax for the other looks fine but its not the value i'm putting in, I want it to use the exact value I put in so I can sign in. How do I do this? I've tried several different cast and convert solutions with no luck.
它运行良好，但是数据库中的代码一个看起来像日语，另一个的语法看起来不错，但它不是我输入的值，我希望它使用我输入的确切值，以便我可以登录。如何我要这样做吗？我尝试了几种不同的转换和转换解决方案，但都没有运气。
回答by Jake Feasel
Try this:
尝试这个：
UPDATE dbo.Login
SET
Salt=CAST('bPftidzyAQik' AS VARBINARY),
Password=0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B
WHERE LoginID=10947
(you don't need to cast a string storing a hex value into varbinary - that's just what it is by default, without the quotes)
（您不需要将存储十六进制值的字符串转换为 varbinary - 默认情况下就是这样，没有引号）
The question is, how are you generating that password hex value, and are you using the same encoding to generate it as you are to read it? If you are planning on converting that data back into a string, you'll need some code to do so. Here's a function I wrote that does this:
问题是，您如何生成该密码十六进制值，并且您是否使用与读取它相同的编码来生成它？如果您打算将该数据转换回字符串，则需要一些代码来执行此操作。这是我编写的一个函数：
CREATE FUNCTION ConvertBinary
(
@value AS varbinary(max)
) RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @result AS varbinary(max),
@result2 AS varchar(max),
@idx AS bigint;
IF @value IS null
return null;
SELECT @result = @value;
SELECT @result2 = '';
SELECT @idx = 1;
WHILE substring(@result, @idx, 1) != 0 AND @idx < len(@result)
BEGIN
SET @result2 = @result2 + cast(substring(@result,@idx,1) as char(1));
SET @idx = @idx + 1;
END
RETURN @result2;
END
I don't know how helpful this will be for you, though, since it's very possible that whatever app is using these fields is handling the values differently than this function expects. For the record, this function takes a varbinary value which was originally utf-8 string, and returns the varchar value of that string. Good luck!
不过，我不知道这对您有多大帮助，因为很可能使用这些字段的任何应用程序处理的值都与此函数预期的不同。作为记录，此函数采用原始 utf-8 字符串的 varbinary 值，并返回该字符串的 varchar 值。祝你好运！
回答by JOpuckman
Since you are using 2008, try this:
由于您使用的是 2008，请尝试以下操作：
UPDATE dbo.Login
SET
Salt=CAST('bPftidzyAQik' AS VARBINARY),
Password=CONVERT(VARBINARY(MAX), '0x2B89C2954E18E15759545A421D243E251784FA009E46F7A163926247FDB945F85F095DBB1FFF5B2B43A6ADAE27B8C46E176902412C4F8943E39528FF94E0DD5B', 1)
WHERE LoginID=10947
