从SQL Server和Oracle获取散列值并比较它们？ [英] Get hash values from SQL Server and Oracle and compare them?

问题描述

是否可以从两个数据库服务器生成哈希代码并对它们进行比较？如何在SQL Server中编写以下伪SQL？特别是两个 getHash 函数，它们接受SQL服务器和oracle中的多重数值/浮点数列。

Is it possible to generate hash code from both database server and compare them? How to write the following pseudo SQL in SQL Server? Especially the two getHash functions which accept mutliple numeric/float columns in SQL server and oracle.

select s.PK
from sqltable s
    join openquery(oracleLinkedServer, 
      'select PK, getHash(Column1, floatColumn2, ..., floatColumnN) oracleHash 
       from oracleTable') o on o.PK = s.PK
where
    getHash(Column1, floatColumn2, ..., floatColumnN) <> oracleHash

推荐答案

$ b

In SQL Server:

select upper(substring(sys.fn_sqlvarbasetostr(hashbytes('MD5','A')),3,32));

结果：

result:

7FC56270E7A70FA81A5935B72EACBE29

在Oracle中：

In Oracle :

select rawtohex(
    DBMS_CRYPTO.Hash (
        UTL_I18N.STRING_TO_RAW ('A', 'AL32UTF8'),
        2)
    ) from dual;

结果：

result:

7FC56270E7A70FA81A5935B72EACBE29

确保您的字符串完全相同（区分大小写）。这里我用'A'作为一个简单的例子，但它可以是任何字符串。

Make sure your strings are exactly the same (case sensitive). Here I used 'A' as a simple example, but it could be any string really.

如果通过转换为大字符串来避免数据类型差异，应该能够在不同平台上生成相同的md5散列。请注意，SQL Server在该哈希前加了一个'0x'来表示十六进制表示，我用该子字符串剥离。

If you avoid data type differences by converting to a big string, you should be able to produce the same md5 hash on different platforms. Note that SQL Server prepended a '0x' to the hash to denote hex representation, which I stripped with the substring.

这篇关于从SQL Server和Oracle获取散列值并比较它们？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！