azure - 当源列为 NULL 或 0 时，用 0 填充 hashbytes 列

Question

我试图在数据类型为 Binary(32) 的哈希字节列中插入值 0。基本上，源中的列可以有值或NULL或0，如果源列值为NULL，我想插入值0(0x0000000000000000000000000000000000000000000000000000000000000000)，如果不插入值则插入0。

declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345  
set @colB = 11111

select convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), src.colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) as hashkey_test 

**Result#1**
| hashkey_colA                                                      | hashkey_colB                                                        |
| ----------------------------------------------------------------- | ------------------------------------------------------------------- |
| 0x69B8185C9FF050D693D06C097229183A1BFB5D95FF8A31FFFD4F234076FF0869| 0x540EFEB2DE09E9415CBFF4DF0439E0C8A4898008B9DC55B1FE5A9451E576B149            |


declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345  
set @colB = 0

select 
case when isnull(@colA,0) = 0 then 0 else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) 
end as hashkey_colA
,case when isnull(@colB,0) = 0 then 0 else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colB)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colB

**Result#2:**
| hashkey_colA                                                      | hashkey_colB                                                        |
| ----------------------------------------------------------------- | ------------------------------------------------------------------- |
| 0x0000000000000000000000000000000000000000000000000000000076FF0869| 0x0000000000000000000000000000000000000000000000000000000000000000            |

我尝试使用 case 语句来评估源值，但是当我使用 case 语句时，哈希键值会发生变化:请参阅 hashkey_colA 的 Result#1 和 Result#2 之间的差异。发生这种情况可能是因为在 TRUE 语句与 FALSE 语句的情况下将考虑最高优先级的数据类型。

如何在使用 case 语句时处理此优先级并确保为 Result#1 和 Result#2 的 hashkey_colA 维护相同的哈希字节值？

Answer 1

在带有 case 语句的第二个 SQL 查询中，而不是给出 when isnull(@colA,0) = 0 then 0 ，将零转换为二进制(32)类型并将语句给出为 when isnull(@colA,0)=0 then convert(binary(32),0)

代码:

declare @colA INTEGER,
        @colB INTEGER 
set @colA = 12345
set @colB = 0
select
case when isnull(@colA,0)=0 then convert(binary(32),0) else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colA)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colA,
case when isnull(@colB,0)=0 then convert(binary(32),0) else 
convert(binary(32),hashbytes('SHA2_256', 
concat(
isnull(rtrim(convert(varchar(100), @colB)),'NA'),
'|', isnull(rtrim(convert(varchar(100), '1')),'NA'), 
'|', isnull(rtrim(convert(varchar(100), '2')),'NA')
)),0) end as hashkey_colB

结果

<表类=“s-表”><标题>hashkey_colAhashkey_colB <正文>0x69B8185C9FF050D693D06C097229183A1BFB5D95FF8A31FFFD4F234076FF08690x0000000000000000000000000000000000000000000000000000000000000000

db<>fiddle