CODEWITHSUNDEEP
mysqlsqlselectsql-like
Ben
Ben

Reputation: 57207

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?

Upvotes: 1

Views: 537

Answers (2)

Tory Netherton
Tory Netherton

Reputation: 753

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300539

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)

Upvotes: 1

Related Questions