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 * FR开发者_运维技巧OM 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?

Actually that's simply not true. It can be done, assuming you have set up full text indexing. Once again, assuming we're dealing with sql server.

http://msdn.microsoft.com/en-us/library/ms142488.aspx

I should point out, since it isn't very clear in the linked document, that you can specify * rather than column names in order to search all of the full text indexed columns in the table.

EDIT: Full text search is available in mysql as well. It's not clear to me whether or not * can be used with it though.

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)