MySQL query String contains

Mysql Problem Overview

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this:

SELECT *
FROM `table`
WHERE `column`.contains('{$needle}')

In PHP, the function is called substr($haystack, $needle), so maybe:

WHERE substr(`column`, '{$needle}')=1

Mysql Solutions

Solution 1 - Mysql

Quite simple actually:

SELECT *
FROM `table`
WHERE `column` LIKE '%{$needle}%'

The % is a wildcard for any characters set (none, one or many). Do note that this can get slow on very large datasets so if your database grows you'll need to use fulltext indices.

Solution 2 - Mysql

Use:

SELECT *
  FROM `table`
 WHERE INSTR(`column`, '{$needle}') > 0

Reference:

INSTR

Solution 3 - Mysql

WHERE `column` LIKE '%$needle%'

Solution 4 - Mysql

Mine is using LOCATE in mysql:

> LOCATE(substr,str), LOCATE(substr,str,pos)

This function is multi-byte safe, and is case-sensitive only if at least one argument is a binary string.

In your case:

SELECT * FROM `table`
WHERE LOCATE('{$needle}', `column`) > 0

Solution 5 - Mysql

In addition to the answer from @WoLpH.

When using the LIKE keyword you also have the ability to limit which direction the string matches. For example:

If you were looking for a string that starts with your $needle:

... WHERE column LIKE '{$needle}%'

If you were looking for a string that ends with the $needle:

... WHERE column LIKE '%{$needle}'

Solution 6 - Mysql

be aware that this is dangerous:

WHERE `column` LIKE '%{$needle}%'

do first:

$needle = mysql_real_escape_string($needle);

so it will prevent possible attacks.

Solution 7 - Mysql

You probably are looking for find_in_set function:

Where find_in_set($needle,'column') > 0

This function acts like in_array function in PHP

Solution 8 - Mysql

The accepted answer would be correct for MySQL alone, but since the question is using:

a variable,
appears to be using {$needle} as a replacement tag, and
it mentions PHP

it appears the author wanted to construct the MySQL query using PHP.

Since the question was asked 12 years ago, current practice would be to use preprepared statements to prevent SQL injection.

Here is an example with PHP:

function check_connection ($user, $pass, $db = 'test', $host = '127.0.0.1', $charset = 'utf8mb4') {
     if (isset($GLOBALS['conn']) && is_object($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof PDO)) {
          if (same_database($db) === true) {
               $connection = &$GLOBALS['conn'];	
	      }
	      else {
		       $GLOBALS['conn'] = pdo_connect($user, $pass, $db, $host, $charset);
		       $connection = &$GLOBALS['conn'];		  
	      }
     }
     else {
          $GLOBALS['conn'] = pdo_connect($user, $pass, $db, $host, $charset);
          $connection = &$GLOBALS['conn'];
     }

     return $connection;
}

function pdo_connect ($user, $pass, $db, $host, $charset){    
     $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
     $options = [
       PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
       PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
     ];
     try {
       return new PDO($dsn, $user, $pass, $options);
     } 
     catch (\PDOException $e) {
       throw new \PDOException($e->getMessage(), (int)$e->getCode());
     }
}

function same_database($db) {
    if (isset($GLOBALS['conn']) && is_object($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof PDO)) {
	    $sql = "SELECT DATABASE() AS 'database'";
	    $sth = $GLOBALS['conn']->prepare($sql);
	    $sth->execute();
	    if (strcasecmp(trim($sth->fetchAll(PDO::FETCH_ASSOC)['0']['database']), trim($db)) === 0) {	
		    return true;
	    }
    }

    return false;
}
    
$conn = check_connection($user, $pass, $db, $host, $charset);

$sql = "
     SELECT *
     FROM `table`
     WHERE `column` like :needle
";

// Concatenating the % wildcard before and after our search variable
$bind = array(
     ':needle' => '%'.$needle.'%'
);

$sth = $conn->prepare($sql);
$sth->execute($bind);

// Being redundant about fetch_assoc incase it was not set in pdo() options
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

// You would use rowCount(), instead of fetchAll(), if it is NOT a SELECT statement
// $sth->rowCount();

print_r($result);

Here are two resources for building PHP PDO statements:

Content Type	Original Author	Original Content on Stackoverflow
Question	arik	View Question on Stackoverflow
Solution 1 - Mysql	Wolph	View Answer on Stackoverflow
Solution 2 - Mysql	OMG Ponies	View Answer on Stackoverflow
Solution 3 - Mysql	chris	View Answer on Stackoverflow
Solution 4 - Mysql	risnandar	View Answer on Stackoverflow
Solution 5 - Mysql	Joshua Powell	View Answer on Stackoverflow
Solution 6 - Mysql	Alejandro Moreno	View Answer on Stackoverflow
Solution 7 - Mysql	Andres	View Answer on Stackoverflow
Solution 8 - Mysql	CommentUser	View Answer on Stackoverflow