Like statement across multiple columns in SQL

Question

I'm trying to query a like statement across multiple columns. I have the following search terms: 'dog' 'cat' 'rabbit' 'gerbil' 'guinea pig' 'hamster'

and I need search for these terms or terms LIKE these in the 'animals' table which has about 40 different columns. I am aware I can do the like statement by doing

 Select * from animals where [animalscolumn] like ('%dog%') or like ('%cat%') or like ('%gerbil%') or like ('%hamster%') or like ('%guinea pig%')

However the 'animalscolumn' isn't the only column I need to run the 'LIKE' statement across. I have to search for these terms in about 40 columns. Would anyone happen to know how? Thanks!

Lentamente

What DBMS are you using? – Tressatressia

You could use a temporary table like this: https://mcmap.net/q/222821/-how-can-i-introduce-multiple-conditions-in-like-operator – Subsidize

Is this a one-off task or a part of a feature? For a one-off task, I'd use copy-paste / contextual replacement in my text editor to write the query with 240 clauses, and throw it away after the one-off task is done. For a feature, I'd look into full-text search capabilities of your database, or maybe into bolting in something like ElasticSearch to your app. – Misalliance

https://mcmap.net/q/1472407/-how-to-iterate-through-multiple-columns-to-search-for-a-value-in-sql-server/330315 or https://mcmap.net/q/1472408/-searching-for-text-over-multiple-columns/330315 or – Berneta

Answer 1

multiple like statements can not be used with or directly. You have to use column name for each like statement.

Use multiple like as mentioned below.

Select * 
from animals 
where 
(
[animalscolumn] like ('%dog%') or 
[animalscolumn] like ('%cat%') or 
[animalscolumn] like ('%gerbil%') or 
[animalscolumn] like ('%hamster%') or 
[animalscolumn] like ('%guinea pig%')
)

Hankow

I think the OP wants to search for the strings in multiple columns; your example searches in only one column. – Shaikh

Nope. he wants to search strings in one column. check his question. only one column mentioned with multiple or conditions. – Hankow

Quoting OP: > However the 'animalscolumn' isn't the only column I need to run the 'LIKE' statement across. I have to search for these terms in about 40 columns. – Abampere

Answer 2

If you want to find a set of number you can use IN

SELECT * 
FROM tableName
WHERE columnId IN (154,156,133,157,119)

Eyla

Answer 3

$sql = "SELECT * from like1 WHERE tutorial_author LIKE '$apply' OR tutorial_title LIKE '$apply'";

Swithbart

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Peipus

Answer 4

if($mode == 'search_contact'){ // $prefix='%';

$apply=$dataObj['search'];
  $data = array();
  // $sql = "SELECT * from add_contact WHERE tutorial_author OR tutorial_title  LIKE '$apply'";
    $sql = "SELECT * from add_contact WHERE
    first_name LIKE '%$apply%'
             OR
    last_name LIKE '%$apply%'
           OR
    title LIKE '%$apply%'
         OR
    company LIKE '%$apply%'
             OR
    address LIKE '%$apply%'";


    $result = $myConnection->query($sql);

  if ($result->num_rows > 0) {

    // print_r($result->fetch_assoc());
    while($row = $result->fetch_assoc()) {
            $row['user_image'] = site_url.upload_dir.$row['image'];
      // print_r($row);
      $data[]=$row;


    }
    $array = array('status'=>true, 'message'=> 'contacts fetched successfully', 'data'=> $data);
          echo json_encode($array);
          exit;
  } else {
    $array = array('status'=>false, 'message'=> "No contacts available" );
          echo json_encode($array);
          exit;
  }

}

Swithbart

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Peipus

Answer 5

Select * from cscart_users 
where status like '%a' and user_type like '%c' and firstname like '%az%';

Vireo

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Peipus

Answer 6

Select * from cscart_users where status like '%a' and user_type like '%c'and firstname like'%az%';

syntax:

select * from tablename where columnname like'%b' and columnname2 like '%g';

Vireo

Answer 7

Here is an example of a SQL Server stored procedure that takes a parameter.

CREATE PROCEDURE [dbo].sp_SearchMultiple @SearchTerm nvarchar(256)  
AS
BEGIN
    SET @SearchTerm = '%' + @SearchTerm + '%'
    SELECT TOP 100
        id, col1, col2, col3
    FROM
        asset_f
    WHERE
        col1 LIKE @SearchTerm OR col2 LIKE @SearchTerm OR col3 LIKE @SearchTerm 
    ORDER BY 
        id ASC
END

Reld

Answer 8

Maybe you could concatenate all the columns and use that with a Regexp search:

SELECT * FROM animals
WHERE concat(column1, column2, column3, ...) ~ '(dog|cat|rabbit|...)'

The tilde is the regexp operator for postgres; if you use a different database type, I think it's REGEXP.

Beatty