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:

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

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

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

Solution 1

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

Use:

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

Reference:

• INSTR

Solution 3

WHERE `column` LIKE '%$needle%'

Solution 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:

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

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

View more solutions

Comments

[chris]

surely LIKE is faster than INSTR?

[OMG Ponies]

@oedo: Depends. LIKE %...% won't use an index if one is present, so they should be equivalent; LIKE ...% would use an index if present. If performance is a real concern, Full Text Search (FTS) would be a better approach.

[arik]

perfect. just what I've been looking for.

[Ryan Shillington]

This will only work if your using a prepared query. If you're using an actual string in there (ex. liquibase sql upgrade script) then consider INSTR mentioned below). This is because if your string contains a % then you'll start matching things with it to.

[Jack]

*Some possible attacks. Also, mysql_real_escape_string is going to be deprecated in future PHP releases.

[cloudworks]

You should use prepared statements, and leave the escaping to PHP. $stmt = $dbh->prepare("Where 'column' LIKE '{:needle}'"); $stmt->bindParam(':needle', $needle); $stmt->execute();

[Sizzling Code]

i know about like queries, and yet today i wanted to find out if certain value exist in string in some column i was googling for it.. Why i never thought of it before??

[angry kiwi]

is this case sensitive?

[Wolph]

@angry_kiwi: with column LIKE '...' it is case insensitive, with column LIKE BINARY '...' it is case sensitive

[Skrol29]

I'm surprised that LIKE is proposed to check for any substring since this operator uses two wildcard characters : % and _. This mean if your string $needle contains one of this special characters then the results is not as expected at all. (-1) for this reply, and (+1) for the INSTR reply.

[Wolph]

@Skrol29: A simple reason for using LIKE instead of INSTR is that LIKE can use an index in the case of LIKE '...%' whereas INSTR will always resort to a table scan.

[Skrol29]

@Wolph: LIKE cannot use indexes since there is a wildcard at the start of the pattern to search. LIKE can use index only for the part of the pattern before the first wildcard. But first of all, this answer simply produce a wrong result.

[Wolph]

@Skrol29: yes, and if you re-read my comment carefully you'll notice that is exactly what I said.

[Wojtek]

'column' should be column (without quotes)

[Wojtek]

when searching for the character _ (underscore) the query LIKE '%_%' doesn't work, for some reason it returns all strings even without _

[Radacina]

I like this solution as in fact there is no way to sort things according to the presence of a substring with like operator . With instr a phrase can be ordered like this select * from table order by instr(col1,"mystring")

[StanE]

Unfortunately, this does not answer the question for me personally, I think. What if the search substring contains the wildcard placeholders - how can I unescape them if I want to include them as-is to the search?. Also, if I remember correctly, there is somewhere in MySQL a setting, which returns results only if the search string is big enough (I think it was by default 3 chars). Is this correct and if yes, is it possible to change this value "client-side" (in the PHP code) somehow?

[Safeer Ahmed]

I wanted to search for _ in a field and it worked. Thanks

[jkmartindale]

@Wojtek _ is a wildcard for any single character, so if you want to search for a literal underscore you will need to escape it. See MySQL LIKE query with underscore.

[Jonas Hoffmann]

Please don't do this with a direct string replacement, as it opens up for SQL injections. Consider using a prepared statement instead.

[Valter Ekholm]

'column' should not be in backticks, right?

[Troy Niemeier]

@ValterEkholm, No. backticks are the normal way to denote an entity in MySQL. (Like a schema, table, table-alias, column, etc.) They're especially useful when you have a column named something that's a keyword or very common/ambiguous term (e.g. DateTime, Date, Status, Name, etc). Wrapping them in backticks makes it explicitly clear that it's an entity, and not a keyword/function/etc.