Full text search on a encrypted column

saji1976
saji1976 used Ask the Experts™
on
Hi,
I have a encrypted column in a table. The fulltext search is not working on this column.
My query is like below,

SELECT * FROM NAMETABLE WHERE Freetext(BriefProfile , '"williams"');
--where BriefProfile column is Encrypted column.
--My query with LIKE OPERATOR is something like below
OPEN SYMMETRIC KEY brief_skey
DECRYPTION BY CERTIFICATE brief_certificate;
SELECT * FROM NAMETABLE   WHERE CONVERT(varchar(5000), DecryptByKey(BriefProfile)) like '%williams%';

How can we perform a fulltext search on the above encrypted column?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Encryption is deterministic. For example smith encrypted the first time could be encrypted as w245234tqwrer, the second time as 46732543456. Then when you are searching on smith, the encyrpted version could be 67554234yu. if you use hashing it may be possible to do that. One way hashes will hash the words in a non-deterministic fashion. Then hash the search phrases so they can find these words.

Commented:
Thanks for your comments.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial