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!

You could use a temporary table like this: http://stackoverflow.com/a/1387797/7667467 — t16n, Mar 06 '17 at 16:33
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. — 9000, Mar 06 '17 at 16:39
http://stackoverflow.com/q/23275971/330315 or http://stackoverflow.com/q/21979334/330315 or — , Mar 06 '17 at 16:48

score 3 · Answer 1 · answered Mar 06 '17 at 16:38

3

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%')
)

answered Mar 06 '17 at 16:38

maulik kansara

1,087
6
21

1

I think the OP wants to search for the strings in multiple columns; your example searches in only one column. – FMFF Jun 18 '20 at 18:32
Nope. he wants to search strings in one column. check his question. only one column mentioned with multiple or conditions. – maulik kansara Jun 19 '20 at 05:28
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. – Seth Aug 28 '23 at 16:37

score 0 · Answer 2 · answered May 26 '21 at 16:22

0

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

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

answered May 26 '21 at 16:22

Hutch

411
10
32

score 0 · Answer 3 · answered Dec 29 '21 at 16:23

0

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

answered Dec 29 '21 at 16:23

mohd Rizwan

1
2

1

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](/help/how-to-answer). – Community Dec 29 '21 at 19:04

score 0 · Answer 4 · answered Dec 30 '21 at 11:31

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;
  }

}

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](/help/how-to-answer). — Community, Dec 30 '21 at 13:49

score 0 · Answer 5 · edited Aug 23 '22 at 11:58

0

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

edited Aug 23 '22 at 11:58

Ondra Žižka

43,948
41
217
277

answered Aug 18 '22 at 12:57

chenchaiah challa

1
1

2

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](/help/how-to-answer). – Community Aug 23 '22 at 11:58

score 0 · Answer 6 · edited Aug 23 '22 at 21:39

0

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';

edited Aug 23 '22 at 21:39

T.S.

18,195
11
58
78

answered Aug 18 '22 at 13:00

chenchaiah challa

1
1

score 0 · Answer 7 · answered Oct 04 '22 at 16:15

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

Like statement across multiple columns in SQL

7 Answers7

Linked