January 21, 2013 at 5:07 am
Hi
I am using an encrypted db, I run a select query but certain columns remain encypted.
To resolve this I used the
CONVERT(nvarchar(200),DECRYPTBYKEY(REG))
but when I filter on REG.
Where name like '%DY%'
It returns some values that don't meet the filter criteria.
What is needed
January 21, 2013 at 5:52 am
robert.nesta123 (1/21/2013)
...but when I filter on REG.
....
You can't filter on REG in this way, because it is encrypted, you can filter only on a decrypted value so:
CONVERT(nvarchar(200),DECRYPTBYKEY(REG)) like '%DY%'
January 21, 2013 at 6:09 am
Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every row in the table to be able to perform the LIKE comparison and return the result set. I've found the performance impact of doing this to be significant.
January 21, 2013 at 6:15 am
Ed Wagner (1/21/2013)
Just as a word of caution here, if you put your DECRYPTBYKEY and CONVERT functions to the left of the operator, they'll both have to run on every row in the table to be able to perform the LIKE comparison and return the result set. I've found the performance impact of doing this to be significant.
But with an encrypted column, you can't do this in another way. With = you can encrypt the searched value and search for equality.
January 21, 2013 at 6:22 am
I know what you mean. I don't know what your requirements are, but if you know the users can search by the first two (and only two) characters of a last name or by some other constant substring, you may be able to store that somewhere else in the table. Or you could store the ASCII values of the first two characters of the string as an integer, index it and then include that field in your WHERE clause. If, however, your specs state that the name has to be completely encrypted, then you're out of luck.
I've seen performance problems when doing something like this when the table gets large and just wanted you to start thinking about it up front so you're not caught off guard later.
January 21, 2013 at 6:27 am
Thanks that worked using the encrypted version in the filter. I thought I had already tried that. I must of got the syntax wrong.
In terms of performance, I'm running on a copy of LIVE in test so not an issue but I will keep it in mind. Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply