CODEWITHSUNDEEP
mysqlsql
jian zhou
jian zhou

Reputation: 113

Search where column value is within a string

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.

Upvotes: 2

Views: 181

Answers (1)

AdamMc331
AdamMc331

Reputation: 16691

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.

Upvotes: 1

Related Questions