How to write query to search particular string in a table which has more column fields in sql-CodePudding

I would like to know how to write sql query to search string in all columns in a table. i.e in single where condition

I have column1, column2,... column50 fields in a table

Right now am using query like

select * from tblist where column1 like '%searchstr%'OR column2 like '%searchstr%' OR ....it goes on

Is there anyway to write sql query to search string in all columns

CodePudding user response：

In the case of your exact query as given, we can try using IN:

SELECT *
FROM tblist
WHERE 'searchstr' IN (column1, column2, ...);

If you really need to use LIKE here, then there is no real shortcut available.

CodePudding user response：

you could save on the grunt work by using the metadata tables to generate your query and then run it. Eg:

select concat('%searchstr% like ',COLUMN_NAME,' OR ')
  from information_schema.columns t
where table_name='t' /*change to the table name*/