Match multiple columns with same SQL value

Question

Suppose there are columns in the table such as akey1 , bkey2 , ckey3 and many others. Is there a way to find common value

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

other than using several AND, OR conditions. It does not matter if the solution is specific to the DBMS.

Answer 1

With the exception of dynamic sql, you will need to specify each of the column names. But you can get a little syntax shortcut and only list the constant once:

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

With dynamic sql, you still have to issue the same query ... but you can at least use string tools to create it first, and if you want to use a wildcard, you can look in the information_schema.columns view for Find them. However, this includes opening and repeating the cursor or returning the column data to the client, any of which involves more work than just listing the column names in the original query. We hope you know your database, at least before you start asking questions.

Answer 2

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 this will be more clear.