CODEWITHSUNDEEP
sql
Mudassir Hasan
Mudassir Hasan

Reputation: 28781

Match multiple columns with same value SQL

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 .

Upvotes: 6

Views: 20683

Answers (2)

Mohammad Atiour Islam
Mohammad Atiour Islam

Reputation: 5708

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416179

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.

Upvotes: 14

Related Questions