A totally hypothetical and insane requirement, incontestably prone to excessive criticism (to put it mildly) from Joe Celko, if he ever happens to visit my blog :)
But, lets just assume that the requirement is warranted and we need a solution in SQL Server 2000.
I have a table with this definition.
CREATE TABLE BAD_ENCRYPTION(VAL_ID INT, VALUE VARCHAR(100))
And the following sample data.
INSERT INTO BAD_ENCRYPTION VALUES(1,'OAMBNRITBAUQZYZZ')
INSERT INTO BAD_ENCRYPTION VALUES(2,'SAQWLR')
INSERT INTO BAD_ENCRYPTION VALUES(3,'GZAXRCBVABGNEM')
INSERT INTO BAD_ENCRYPTION VALUES(4,'CAOWLELYEHCGTHOLR@')
If, you didn't guess the encryption strategy, the four rows will have the value OMNIBUZZ, SQL, GARBAGE, COLLECTOR if you knock off the letters in the even numbered positions.
Now, I need a query that will accept the unencrypted word and find the corresponding ID. Say, I search for GARBAGE, I need to get the value 3.
- Lets say there are about 50,000 rows.
- And my search will result in only one row.
- I can change my table design, if necessary
- I cannot have a computed column having the actual value (then the whole point of this rudimentary encryption is lost).