Reputation: 642
Is it possible to create a SQL query returning a row which has any column matching input. For example:
SELECT row WHERE ANY column LIKE input
Thank you
Upvotes: 0
Views: 1552
Reputation: 16609
Yes it is:
SELECT * FROM MyTable WHERE ColumnA LIKE '%input%' OR ColumnB LIKE '%input%'
If you want literally to dynamically check any column then you need to dynamically build up the list of columns and use a prepared statement.
This will use the INFORMATION_SCHEMA.COLUMNS
table to find the columns in the table, then use GROUP_CONCAT()
to put them into a string separated by the rest of the where clause. By itself this will give you:
ColumnA LIKE '%input%' OR ColumnB
Then you just need to CONCAT()
that with the start of the statement (SELECT .. WHERE
) and the LIKE
for the last column. All this gets stored in a variable @sql
and used as the SQL for the prepared statement:
SET @sql:=(SELECT CONCAT('SELECT * FROM MyTable WHERE ', GROUP_CONCAT(COLUMN_NAME SEPARATOR ' LIKE ''%test%'' OR '), ' LIKE ''%test%''') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'MyTable');
# for debugging only
SELECT @sql;
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
Here is a working SQL Fiddle to demonstrate this: http://sqlfiddle.com/#!2/f475d/22
Upvotes: 1
Reputation: 93764
Use In operator
. since there is no wildcards
in your like operator
you can try something like this.
select row from table where input in (a,b)
Upvotes: 0
Reputation: 2379
I think you are searching for this...
SELECT * from table WHERE column a LIKE '%input%' or column b LIKE '%input%'
Upvotes: 1