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 stackoverflow.com/questions/14116221/… — Michael Berkowski
– Michael Berkowski, Commented Jan 2, 2013 at 2:42
Its asking for single column named foo . I asked this question after seeing that question. — Mudassir Hasan
– Mudassir Hasan, Commented Jan 2, 2013 at 2:45
Ultimately they are the same question (after getting clarifications from the OP) But your question is clearer. — Michael Berkowski
– Michael Berkowski, Commented Jan 2, 2013 at 2:46
possible duplicate of Wildcard for a column name in the WHERE clause of a SELECT statement? — Michael Berkowski
– Michael Berkowski, Commented Jan 2, 2013 at 2:46

Joel Coehoorn · Accepted Answer · 2013-01-02 02:52:29Z

14

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 2, 2013 at 2:52

answered Jan 2, 2013 at 2:45

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vkrishna17 Over a year ago

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.

Joel Coehoorn Over a year ago

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).

Mohammad Atiour Islam · Accepted Answer · 2015-07-31 13:42:13Z

0

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.

answered Jul 31, 2015 at 13:42

Mohammad Atiour Islam

5,7783 gold badges47 silver badges49 bronze badges

Collectives™ on Stack Overflow

Match multiple columns with same value SQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related