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 .

What??? Someone else asked the exact same thing not ten minutes ago http://stackoverflow.com/questions/14116221/mysql-correct-syntax-to-use-for-basic-where-clause — Michael Berkowski, Jan 02 '13 at 02:42
Its asking for single column named `foo` . I asked this question after seeing that question. — Mudassir Hasan, Jan 02 '13 at 02:45
Ultimately they are the same question (after getting clarifications from the OP) But your question is clearer. — Michael Berkowski, Jan 02 '13 at 02:46
possible duplicate of [Wildcard for a column name in the WHERE clause of a SELECT statement?](http://stackoverflow.com/questions/10602888/wildcard-for-a-column-name-in-the-where-clause-of-a-select-statement) — Michael Berkowski, Jan 02 '13 at 02:46

Joel Coehoorn · Accepted Answer · 2013-01-02T02:52:29.037

13

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.

edited Jan 02 '13 at 02:52

answered Jan 02 '13 at 02:45

Joel Coehoorn

380,066
110
546
781

This query performance will be very low. I would suggest executing a query something like this "select * from table where akey1 = 'xyz' or bkey2='xyz' or ckey3='xyz'; " where each column is having indexing. – vkrishna17 Jun 04 '18 at 11:18
That will usually produce basically the same execution plan, and I suspect your understanding of how indexing works is flawed. You can't index those columns individually and expect it to help. Typically only one index on a table can be used in a given query for that table (though it is possible for the query engine to rewrite this as a bunch of UNIONs, and in that case the multiple indexes can help... but that's not the norm). – Joel Coehoorn Jun 04 '18 at 20:20

score 0 · Answer 2 · answered Jul 31 '15 at 13:42

You can also try this approach

ALTER PROCEDURE USP_GetClinicNameList
  
  @SearchStr varchar(50)
AS
BEGIN

SET @SearchStr = RTRIM(@SearchStr) + '%'

SELECT TOP (10)
 *
FROM clinic c
WHERE c.cclinicname LIKE @SearchStr
OR c.caddress1 LIKE @SearchStr
OR c.caddress2 LIKE @SearchStr
OR c.ccity LIKE @SearchStr
OR c.cstate LIKE @SearchStr
OR c.cclinicid LIKE @SearchStr
OR c.czip LIKE @SearchStr
OR c.ccliniccode LIKE @SearchStr
OR c.cphone LIKE @SearchStr
ORDER BY c.cclinicname

END
GO

hope it will be more understandable.

Match multiple columns with same value SQL

2 Answers2

Linked