SQL search multiple values in same field

Question

I'm building a simple search algorithm and I want to break my string with spaces, and search my database on it, like so:

$search = "Sony TV with FullHD support";  
$search = explode( ' ', $search );

SELECT name FROM Products WHERE name LIKE %$search[1]% AND name LIKE %$search[2]% LIMIT 6

Is this possible?

score 86 · Accepted Answer · answered Apr 26 '13 at 15:32

86

Yes, you can use SQL IN operator to search multiple absolute values:

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

answered Apr 26 '13 at 15:32

Glitch Desire

14,632
7
43
55

1

Whilst this answers your question, you're likely to want something more complicated than either of our solutions, searching `OR` but displaying most relevant (most matches) results first. – Glitch Desire Apr 26 '13 at 15:39
I am using SQL Server and trying to do the same thing. However, I am passing the search string as a parameter to a stored procedure. How do I break up the search string paramater based on spaces and then add all of the "LIKE '%Value1% OR ....." to the where clause when I don't know the values before the user searches? – Kevin Quiring Oct 26 '16 at 16:57
I'm pretty sure in most simple cases `AND` is the correct search. If a user bothered typing in a word, they probably want it to to show up in the result 90% of the time. Think Google search: [+"bob" +"hope"] which would be absolutely useless as an `OR` search. And pardon Google for the completely ridiculous quoting requirements in semi-useful searches. Also, `AND` is unique functionality difficult to obtain, while `OR` you could just run 2 searches and see if one gets a hit. – ebyrob Mar 17 '17 at 15:22

score 5 · Answer 2 · answered Apr 26 '13 at 15:40

Try this

Using UNION

$sql = '';
$count = 0;
foreach($search as $text)
{
  if($count > 0)
     $sql = $sql."UNION Select name From myTable WHERE Name LIKE '%$text%'";
  else
     $sql = $sql."Select name From myTable WHERE Name LIKE '%$text%'";

  $count++;
}

Using WHERE IN

$comma_separated = "('" . implode("','", $search) . "')";  // ('1','2','3')
$sql = "Select name From myTable WHERE name IN ".$comma_separated ;

score 1 · Answer 3 · answered Apr 02 '19 at 20:00

This will works perfectly in both cases, one or multiple fields searching multiple words.

Hope this will help someone. Thanks

declare @searchTrm varchar(MAX)='one two three four'; 
--select value from STRING_SPLIT(@searchTrm, ' ') where trim(value)<>''
select * from Bols 
WHERE EXISTS (SELECT value  
    FROM STRING_SPLIT(@searchTrm, ' ')  
    WHERE 
        trim(value)<>''
        and(    
        BolNumber like '%'+ value+'%'
        or UserComment like '%'+ value+'%'
        or RequesterId like '%'+ value+'%' )
        )

score 1 · Answer 4 · edited Mar 02 '21 at 20:14

1

You can try and execute below query:

SELECT name FROM Products WHERE REGEXP '.*Value1|.*Value2';

Pls note that there should not be a space before or after the pipe symbol (|).

edited Mar 02 '21 at 20:14

Mohit Kumar

2,898
3
21
34

answered Mar 01 '21 at 16:39

Kunal Wadhwa

11
4

score 0 · Answer 5 · answered May 18 '18 at 03:48

This has been partially answered here: MySQL Like multiple values

I advise against

$search = explode( ' ', $search );

and input them directly into the SQL query as this makes prone to SQL inject via the search bar. You will have to escape the characters first in case they try something funny like: "--; DROP TABLE name;

$search = str_replace('"', "''", search );

But even that is not completely safe. You must try to use SQL prepared statements to be safer. Using the regular expression is much easier to build a function to prepare and create what you want.

function makeSQL_search_pattern($search) {
    search_pattern = false;
    //escape the special regex chars
    $search = str_replace('"', "''", $search);
    $search = str_replace('^', "\\^", $search);
    $search = str_replace('$', "\\$", $search);
    $search = str_replace('.', "\\.", $search);
    $search = str_replace('[', "\\[", $search);
    $search = str_replace(']', "\\]", $search);
    $search = str_replace('|', "\\|", $search);
    $search = str_replace('*', "\\*", $search);
    $search = str_replace('+', "\\+", $search);
    $search = str_replace('{', "\\{", $search);
    $search = str_replace('}', "\\}", $search);
    $search = explode(" ", $search);
    for ($i = 0; $i < count($search); $i++) {
        if ($i > 0 && $i < count($search) ) {
           $search_pattern .= "|";
        }
        $search_pattern .= $search[$i];
    }
    return search_pattern;
}

$search_pattern = makeSQL_search_pattern($search);
$sql_query = "SELECT name FROM Products WHERE name REGEXP :search LIMIT 6"
$stmt = pdo->prepare($sql_query);
$stmt->bindParam(":search", $search_pattern, PDO::PARAM_STR);
$stmt->execute();

I have not tested this code, but this is what I would do in your case. I hope this helps.

score 0 · Answer 6 · edited Sep 09 '21 at 03:25

I know this is long time ago, but I have a solution. It can solved like this:

#intial query 

query = 'SELECT var1, var2 FROM dbo.db_name WHERE'

if status :

        query = query + " AND status='" + status + "'"

if type :

        query = query + " AND Type='" + type + "'"  

if number :

        query = query + " AND Number='" + number + "'"

if cancel_request:

        query = query + " AND CancelRequest='" + cancel_request + "'"


query = query + ' ORDER BY transid DESC'


cur.execute(query)

SQL search multiple values in same field

6 Answers6

Linked

Related