Search for value in SQL row

I want to find a value in an sql row. But I want it to select from any part of the row. For instance:

SELECT * FROM posts WHERE everything = cake

And then I want it to find cheesecake, because it has the value cake in it

If you mean have the query select a row that has a particular single field with a value of “cheesecake” returned because it has the substring “cake”, that can be done by using wildcards. eg.
WHERE field LIKE '%cake%'

If you mean more than a single field, I know of no way to do this without doing each possible field. eg. if there was a row like

firstname - lastname - county - city 
Robert - Robertson - Robertshire - Robertville 

and you wanted to find “Rob” in any of the four fields, the query would need to be like

WHERE firstname LIKE '%Rob%' 
OR lastname LIKE '%Rob%' 
OR county LIKE '%Rob%' 
OR city LIKE '%Rob%' 

Repetitive I know, but that’s how it would be done. Note that using wildcards - especially leading wildcards - can impact performance. So you may want to have those fields indexed and even then I think keeping the wildcard use to a minimum would be a good idea.

Check your query with an EXPLAIN to see if it triggers a full table scan.

1 Like

Another way (no idea about the performance).

select * from book where concat(year, author, title) like '%something%'

And yet another way is to use full text search.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.