Checking if a string is found in one of multiple columns in mySQL

Question

Hi,

I need to check if a string is found in one or more columns.

Basically, I have a program which lets you checks multiple fields (name, surname, etc...)

If both name and surname are checked and the user enters just the name, for example chris it would be easy to check it in mySQL with the LIKE parameter like this:

select * from tblClients WHERE name LIKE '%john%';

This obviously works. However, what I need to do is that if both name and surname are checked it would do something like this:

select * from tblClients WHERE (name or surname) LIKE '%john%';

I want that if this logic is made when there is a client named john doe, the second command will still find that client.

I tried this command and no syntax errors were found, however, 0 results where returned.

Any suggesstions please?

Answer 1

Can't you just use separate WHERE clauses, such as:

SELECT * FROM tblClients
WHERE (name LIKE '%john%')
OR (surname LIKE '%john%')

You're having to amend the SQL based on which columns the user selects anyway.

Answer 2

You'll need to do this:, as SQL will choke on the (name or surname) part

select * from tblClients WHERE name LIKE '%john%' or surname LIKE '%john%';

I'm not sure what language your using, but in psuedocode, you can make this a little easier with a simple function

query = [whatever you are searching for]
var columns = array('name', 'surname')
var where = array
foreach columns as column
    where[] = column + ' like "%' + query + '%"'

sql = "select * from tblClients WHERE " + join(where, ' OR ');

//where join joins values of an array into a string

Answer 3

Instead of having separate LIKE-clauses, you can concatenate the columns:

select * 
from tblClients 
WHERE name || surname LIKE '%john%'

This leaves some edge case (name = 'jo' and surname = 'hn' would be a match), if you're concerned about this you can add a separator between the columns

WHERE name || ' ' || surname LIKE '%john%'

I'm not sure about the performance impact of the two choices, but a LIKE with a percent at the start and the end will not use an index, so I don't think there will be problems.

Answer 4

I managed to program it.

Here is what I did:

    $like = "";
    foreach ($fields as $field)
 {
  if ($like == "")
  {
   $like = $field . " LIKE '%" . $query . "%' or ";
  }
  else
  {
   $like = $like . $field . " LIKE '%" . $query . "%' or ";
  }
 }
 $like = substr_replace($like, "", -3);

This way, all ORs where insterted in a for loop and appended to a string. Then I programmed the SQL string like this:

$sql  = "select * ";
$sql .= " from $table where  " . $like

Hope this helps somebody else :)

Thanks for your posts.

Answer 5

I recommend using UNIONs over ORs - ORs are notorious for poor performance, and risk maintenance issues if not properly understood:

SELECT c.* FROM tblClients c WHERE c.name LIKE '%john%'
UNION
SELECT c.* FROM tblClients c WHERE c.surname LIKE '%john%'

Keep in mind that UNION will return a distinct list - duplicates will be removed, but it will perform slower than using UNION ALL (which will not remove duplicates). Use what suits your purpose