MySQL query String contains

Question

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:

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

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

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

Answer 1

Quite simple actually:

mysql_query("
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.

Answer 2

Use:

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

Reference:

• INSTR

Answer 3

WHERE `column` LIKE '%$needle%'

Answer 4

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:

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

Answer 5

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

Answer 6

be aware that this is dangerous:

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

do first:

$needle = mysql_real_escape_string($needle);

so it will prevent possible attacks.

Answer 7

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