Posted by Kasper Tidemann on Tuesday 26th of October 2010 12:06:25 AM
Say you want to execute a SELECT * FROM blah WHERE <some fields> LIKE ‘%<some words>%’. To accomplish this, you can 1) concatenate the values of the fields you wish to search and 2) use the % operator to combine the queries. Have a look at this example:
SELECT * FROM your_table WHERE (field_1 || ‘ ‘ || field_2 LIKE ‘%apples%oranges%’);
Notice how I concatenate field_1 and field_2 using the || operator (double-pipe). This is especially useful if you’ve got a table containing some users, and they’ve got a firstname and a lastname. If you want to search for “kasper tidemann” combining both the firstname and lastname in one string, do this:
SELECT * FROM users WHERE (firstname || ‘ ‘ || lastname LIKE ‘%kasper tidemann%’);
Instead of searching firstname for “kasper tidemann” and then lastname for “kasper tidemann”, you search the full name this way.
It works fine and dandy, and it’s an easy, fast way of solving a simple problem. Gotta love SQL.