Search where column value is within a string

Question

Consider this database table:

| id | name |    city   |
+----+------+-----------+
| 10 | test | anza      |
| 11 | fish | riverside |

I want to find items whos city name is included in a string like:

anza city

riverside county

How can I find items whose city name is included in my strings using MySQL? In other words, which cities are included in the string 'anza city'? This should return id 10.

It seems the opposite of LIKE in MySQL.

Can you put your current query here? – Márcio Gonzalez Apr 30 '15 at 17:15 — Márcio Gonzalez, Apr 30 '15 at 17:15

score 1 · Accepted Answer · edited May 23 '17 at 12:22

This is not the opposite of LIKE. I imagine the reason you feel that way is because you're used to seeing something like this:

WHERE columnName LIKE 'someString';

However, you can still use this but you need to use some manipulation on the column. If you want to check that the column name is within your string, just add wildcards at the front and back, and use LIKE in this way:

SELECT *
FROM myTable
WHERE 'anza city' LIKE CONCAT('%', city, '%');

Note that the value with the wild cards will go on the right side of the like. If you said CONCAT('%', city, '%') LIKE 'anza city' nothing will be returned.

Here is an SQL Fiddle example.

EDIT

Here is more information regarding searching for a substring.

Fun fact, this is my 300th Stack Overflow answer! I hope it helps you.

Search where column value is within a string

1 Answers1