Search code examples
mysqlsqlselectsql-like

SQL: Using LIKE, return row if search matches any value in that row

I'm trying to make a query where my LIKE can match any field in the row.

To put it another way, I don't want to explicitly set column names to match each LIKE condition.

So, like this:

SELECT * FROM mytable WHERE any_field_value LIKE 'str%'

NOT like this:

SELECT * FROM mytable WHERE col_a LIKE 'str%' OR col_b LIKE 'str%'

and so on.

Surely, not an uncommon request? Can someone show me the light?

Solution

  • Can only be done using dynamic SQL (unless using full text indexing).

    Before you do that, I suggest reading: The Curse and Blessings of Dynamic SQL (assuming SQL Server)