Match multiple columns with same value SQL

Question

Suppose the table has columns like akey1 , bkey2 , ckey3 and many more like it.
Is there way to search for a common value

SELECT * FROM table WHERE %key% LIKE 'xyz'

other than using multiple AND , OR conditions . Doesn't matter if solution is DBMS specific .

Answer 1

Short of dynamic sql, you will have to spell out each of the column names. But you can get a bit of syntactic shortcut and only list the constant once:

SELECT * FROM table WHERE 'xyz' IN (akey1, bkey2, ckey3)

With dynamic sql, you still have to issue this same query... but you can at least use string tools to build it up first, and if you want to use wildcard matching you can look in the information_schema.columns view to find them. However, that involves opening and iterating over a cursor or returning the column data to the client, either of which involves more work than just listing out column names in the original query. Hopefully you know your database at least that well before you start issues queries to it.