If you don't like (or it isn't flexible enough) the idea of denormalizing and adding an additional column for the last four (which you might not even need to encrypt), the only thing I can think of is to load a BO with all the records, probably just the SSN and PK and then use this to filter the results returned. I guess what you could do in the searching event is get the partial SSN entered and how the user is comparing this to the SSN field (string search, so starts with, ends with, contains, equals), use that to retrieve all the PKs that match the indicated comparison from the BO with PKs and SSNs. Then add a where clause that would check if the PK is one of the PKs that match the SSN (using an IN()). Not very pretty....



I'd question why the data is encrypted within a field in the first place...I'm sure this is out of your control, but seems over kill since the db should have security in place and the application should have security in place to restrict access.

yup nothing pretty about it, I have no control over it either, they have to be encrypted at the database level. Thank you for the comments and ideas, I'll let you know how it works out

Keith Chisarik

Good luck!