CODEWITHSUNDEEP
mysqlsqldatabase
Engineering Machine
Engineering Machine

Reputation: 642

Return row where any column matches input

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

Answers (3)

Rhumborl
Rhumborl

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

Pரதீப்
Pரதீப்

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

Dhaval
Dhaval

Reputation: 2379

I think you are searching for this... 

  SELECT * from table  WHERE column a LIKE '%input%' or column b LIKE '%input%'

Upvotes: 1

Related Questions