问题描述
我有一个很大的nvarchar,我希望传递给HashBytes函数。
我得到错误:
我发现这是由于HashBytes函数的最大限制为8000字节。进一步的搜索向我展示了一个'解决方案',在这里我的大型varchar将被分割和散列,然后再与这个用户定义的函数结合:
function [dbo]。[udfLargeHashTable](@algorithm nvarchar(4),@InputDataString varchar(MAX))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE
@Index int,
@InputDataLength int,
@ReturnSum varbinary(max),
@InputData varbinary(max)
SET @ReturnSum = 0 $ b @ b SET @Index = 1
SET @InputData = convert(binary,@ InputDataString)
SET @InputDataLength = DATALENGTH(@InputData)
WHILE @Index< = @InputDataLength
BEGIN
SET @ReturnSum = @ReturnSum + HASHBYTES(@algorithm,SUBSTRING(@InputData,@Index,8000))
SET @Index = @Index + 8000
END
RETURN @ReturnSum
END
我打电话给:
set @ ReportDefinitionHash = convert(int,dbo。[udfLargeHashTable]('S HA1',@ ReportDefinitionForLookup))
其中@ReportDefinitionHash是int,@ReportDefinitionForLookup是varchar
传递一个简单的字符'test'会产生与我的UDF不同的int,而不是正常的HashBytes调用。
关于这个问题的任何建议?
只要使用这个函数(取自):
create function dbo.fn_hashbytesMAX
(@string nvarchar(max)
,@Algo varchar(10)
)
返回varbinary(20)
为
/ ***************************** *******************************
*
*作者:Brandon Galderisi
*最后修改:15-SEP-2009(由Denis)
*目的:使用系统功能hbytes以及
*作为sys.fn_varbintohexstr来分割
* nvarchar(max)字符串和散列在8000字节中
*块散列每个8000字节块,
*获得40字节输出,将每个
* 40字节的输出流输出到一个字符串中,然后散列
*该字符串。
*
***************************************** ******************** /
begin
declare @concat nvarchar(max)
,@ NumHash int
, @HASH varbinary(20)
set @NumHash = ceiling((datalength(@string)/ 2)/(4000.0))
/ * HashBytes只支持8000字节,所以如果字符串较大, /
if @ NumHash> 1
begin
- #* 4000字符串
; with a(选择1作为n union all选择1) - 2
,b as(从a,a1选择1作为n) - 4
,c as(从b选择1作为n,b b1) - 16
,d as(select 1 as n从c,c c1) - 256
,e as(从d中选择1作为n,d d1) - 65,536
,f as(从e中选择1作为n,e e1) - - 4,294,967,296 = 17+ TRILLION字符
,计为(选择row_numb $($)
,因子为(选择rn,(rn * 4000)+1因子分解)
选择@concat = cast((
select right(sys.fn_varbintohexstr
(
hashbytes(@Algo,substring(@string,factor - 4000,4000))
)
,40)+' '
来自因素
其中rn 用于xml路径('')
)as nvarchar(max))
set @HASH = dbo.fn_hashbytesMAX(@concat,@ Algo)
end
else
begin $ b $ set @HASH = convert(varbinary(20),hashbytes(@Algo ,@string))
end
return @HASH
end
结果如下:
select
hashbytes('sha1',N'test ') - 带nvarchar输入
的原生函数,hashbytes('sha1','test ) - 使用varchar输入的原生函数
,dbo.fn_hashbytesMAX('test','sha1') - 加入到nvarchar输入的函数
,dbo.fnGetHash('sha1','test' ) - 您的功能
输出:
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0xA94A8FE5CCB19BA61C4C0873D391E987982FBBD3
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0x00000000AE6DBA4E0F767D06A97038B0C24ED720662ED9F1
I have quite a large nvarchar which I wish to pass to the HashBytes function.I get the error:
Being ever resourceful, I discovered this was due to the HashBytes function having a maximum limit of 8000 bytes. Further searching showed me a 'solution' where my large varchar would be divided and hashed seperately and then later combined with this user defined function:
function [dbo].[udfLargeHashTable] (@algorithm nvarchar(4), @InputDataString varchar(MAX))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE
@Index int,
@InputDataLength int,
@ReturnSum varbinary(max),
@InputData varbinary(max)
SET @ReturnSum = 0
SET @Index = 1
SET @InputData = convert(binary,@InputDataString)
SET @InputDataLength = DATALENGTH(@InputData)
WHILE @Index <= @InputDataLength
BEGIN
SET @ReturnSum = @ReturnSum + HASHBYTES(@algorithm, SUBSTRING(@InputData, @Index, 8000))
SET @Index = @Index + 8000
END
RETURN @ReturnSum
END
which I call with:
set @ReportDefinitionHash=convert(int,dbo.[udfLargeHashTable]('SHA1',@ReportDefinitionForLookup))
Where @ReportDefinitionHash is int, and @ReportDefinitionForLookup is the varchar
Passing a simple char like 'test' produces a different int with my UDF than a normal call to HashBytes would produce.
Any advice on this issue?
Just use this function (taken from Hashing large data strings with a User Defined Function):
create function dbo.fn_hashbytesMAX
( @string nvarchar(max)
, @Algo varchar(10)
)
returns varbinary(20)
as
/************************************************************
*
* Author: Brandon Galderisi
* Last modified: 15-SEP-2009 (by Denis)
* Purpose: uses the system function hashbytes as well
* as sys.fn_varbintohexstr to split an
* nvarchar(max) string and hash in 8000 byte
* chunks hashing each 8000 byte chunk,,
* getting the 40 byte output, streaming each
* 40 byte output into a string then hashing
* that string.
*
*************************************************************/
begin
declare @concat nvarchar(max)
,@NumHash int
,@HASH varbinary(20)
set @NumHash = ceiling((datalength(@string)/2)/(4000.0))
/* HashBytes only supports 8000 bytes so split the string if it is larger */
if @NumHash>1
begin
-- # * 4000 character strings
;with a as (select 1 as n union all select 1) -- 2
,b as (select 1 as n from a ,a a1) -- 4
,c as (select 1 as n from b ,b b1) -- 16
,d as (select 1 as n from c ,c c1) -- 256
,e as (select 1 as n from d ,d d1) -- 65,536
,f as (select 1 as n from e ,e e1) -- 4,294,967,296 = 17+ TRILLION characters
,factored as (select row_number() over (order by n) rn from f)
,factors as (select rn,(rn*4000)+1 factor from factored)
select @concat = cast((
select right(sys.fn_varbintohexstr
(
hashbytes(@Algo, substring(@string, factor - 4000, 4000))
)
, 40) + ''
from Factors
where rn <= @NumHash
for xml path('')
) as nvarchar(max))
set @HASH = dbo.fn_hashbytesMAX(@concat ,@Algo)
end
else
begin
set @HASH = convert(varbinary(20), hashbytes(@Algo, @string))
end
return @HASH
end
And the results are as following:
select
hashbytes('sha1', N'test') --native function with nvarchar input
,hashbytes('sha1', 'test') --native function with varchar input
,dbo.fn_hashbytesMAX('test', 'sha1') --Galderisi's function which casts to nvarchar input
,dbo.fnGetHash('sha1', 'test') --your function
Output:
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0xA94A8FE5CCB19BA61C4C0873D391E987982FBBD3
0x87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1
0x00000000AE6DBA4E0F767D06A97038B0C24ED720662ED9F1
这篇关于SQL Server 2008和HashBytes的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!