SQL Server FullText searching on text that is slightly obfuscated before insert Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the data in Management Studio; not really preventing shoulder surfing, more like "can't read text even with correct permissions")

Here's a simplified example.


In the website I input HELLO into a textbox and click save.
The save method then replaces each character with the next in the
alphabet, so HELLO becomes IFMMP.
The save/update command is then called and IFMMP is saved into the
database.
When the data is read back from the database, each character is
replaced with the one before, so IFMMP becomes HELLO and gets
displayed in the textbox.


IFMMP won't be much good for fulltext searching. We have hundreds of existing sprocs &amp; triggers which we don't want to update for this.

The question is, how can I "un-obfuscate" the data before it gets added to the Fulltext catalogue?

Update: Looks like we need a custom WordBreaker or IFilter. Will update if get to a solution.
The full text catalog will only reflect the contents of a table's data.

Since it sounds like you're not using full text search yet, and you just want to start, you basically have 2 ways to do this:

Option 1: add columns with the de-obfuscated data, and use full text search on 
those. Say you have a column dbo.Customers.SavedText that is obfuscated - you add a dbo.Customers.SavedTextClear column with the original text. You can populate it and keep it up to date with triggers on insert and update. (Triggers usually have a bad reputation - rightfully so, you wanna minimize the work performed in them - but you asked for a solution that didn't require modifying the original queries inserting/updating into the table, and this is what it looks like. When you tie my hands, my artwork isn't quite as good, ha ha ho ho.)

Option 2: do full text search in something like Elasticsearch. Build an ETL process that exports the fields you want to search, de-obfuscates them, and puts them in a database that's tuned for full text search, like Elastic. You can read about how Stack Overflow approached this problem, although when you read it, I bet modifying your original queries is probably going to sound like a better solution. The only reason I mention it is that if you really need to scale out, this can be a less expensive approach long term.