SQL Server : CASE statement varbinary comparison has unexpected behavior

Question

UPDATE: It appears there may be some certificate or rule running on open queries with enrypted column data. I have discovered that the following produces an unencrypted value concat 'more'. I will have to verify with our DBA what may cause this behavior.

case when s.EncryptedColumn is not null then concat(s.[EncryptedColumn], ' more') else s.[RawColumn] end

I am trying to do a simple comparison to null against a varbinary(16) column however I cannot get the result to return true.

This is what I have tried:

Attempt 1:

select 
    s.[EncryptedColumn], 
    (case when s.[EncryptedColumn] is not null 
            then s.[EncryptedColumn] 
            else s.[RawColumn] 
     end) as 'result'

Result 1: encrypted data, raw data

Attempt 2:

select 
    datalength(s.[EncryptedColumn]), 
    (case when datalength(s.[EncryptedColumn]) > 0 
            then s.[EncryptedColumn] 
            else s.[RawColumn] 
     end) as 'result'

Result 2: encrypted data length (16), raw data

Any ideas?

Not clear what you are asking. Where are these results? I don't see how the text after result1 is correlated with the query in attempt 1? — Martin Smith, Dec 28 '15 at 22:04
Does `coalesce(EncryptedColumn, RawColumn)` do what you need? `datalength(null)` is null, by the way. So that's probably one problem with the second attempt. — shawnt00, Dec 28 '15 at 22:23
Sorry for the confusion @MartinSmith. I have a non-null encrypted column and both selects return evidence that it is not null in the first column selected. Only the result of the case is always false. — nicky, Dec 28 '15 at 22:27
@marc_s, thank you for the formatting edit. I am using SQL Server 2014 — nicky, Dec 28 '15 at 22:29
It might be helpful to get a sense of the values you're dealing with. The first attempt suggests you really want a `coalesce()`. The second suggests maybe you're doing `datalength(0x)`. Is there more to the story? I don't want to see the data you've got encrypted of course. — shawnt00, Dec 28 '15 at 22:34
@shawnt00, (1) encrypted column data is 0x343030303034313131 with a datalength of 9 (2) coalesce(EncryptedColumn, RawColumn) produced the the raw column data. — nicky, Dec 28 '15 at 22:38
Is it possible that you've got a union or some other kind of thing going on in the full query that inadvertently transposed the columns and/or gave them aliases that you weren't expecting? Everything looks good to me unless SQL Server 2014 added some weird null settings that I don't know about yet. — shawnt00, Dec 28 '15 at 22:45
Interestingly, casting as an int results in 'yes'. _case when cast(s.EncryptedColumn as int) > 0 then 'yes' else 'no' end_ But does not return the encrypted column data when attempting: _case when cast(s.EncryptedColumn as int) > 0 then s.[EncryptedColumn] else s.[RawColumn] end_ — nicky, Dec 28 '15 at 22:45

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

If I got it right, you should take a look here:

use ISNULL(DATALENGTH(Content), -1) instead, so that you can distinguish between length 0 and NULL. Or just use DATALENGTH(Content)

edited May 23 '17 at 12:23

Community

1
1

answered Dec 28 '15 at 22:10

Alexei - check Codidact

22,016
16
145
164

score 0 · Answer 2 · answered Dec 28 '15 at 22:58

UPDATE: It appears there may be some certificate or rule running on open queries with enrypted column data. I have discovered that the following produces an unencrypted value concat 'more'. I will have to verify with our DBA what may cause this behavior.

case when s.EncryptedColumn is not null then concat(s.[EncryptedColumn], ' more') else s.[RawColumn] end

SQL Server : CASE statement varbinary comparison has unexpected behavior

2 Answers2