How to search for city in a record in MySQL

Question

I have to search user according by city but my problem is that in user table in city field there are two cities like arizona@losvegas, because in registration user can select two cities.

So how can I search city by city name?
Like if someone searches for all users from arizona...

I have done this by using LIKE in SELECT query but I want some other method to do this.

Answer 1

Well, the obvious way is using like. I don't know why you wouldn't want to use it:

select * from users where city like '%arizona%';

Maybe you can tell why are you trying to avoid LIKE and someone will try to give you some ideas. If what you need is a better pattern matcher than LIKE you can use REGEXP operator.

Answer 2

I'm taking a wild guess here and assuming that the posters problem with like is, that it may also match cities that are contained in other cities (cannot think of such example however). To overcome this, one could use three predicates like this:

WHERE city LIKE 'arizona@%' 
OR city like '%@arizona'
OR city like 'arizona'

In my opinion however, combining two cities in one column is bad design in the first place. Why not either make two columns, make a row for each city or make an extra table that links the cites to the users.

Answer 3

I'm a bit confused as to why the database doesn't have multiple city tuples for each user. But, if you want a more powerful way to do searching in MySQL, you can search through text in columns with regular expressions:

SELECT name FROM employees WHERE name RLIKE 'P$'

This matches employee names that end in P since the $ is the end of string anchor. You can also do things like:

SELECT username FROM userTable WHERE city RLIKE '[Mm]assachusett?s'

... if, heaven forbid, people don't know Massachusetts has two 't's or something.

Regular expressions are very powerful so if you want to use this, you can. But to be honest, considering what you're looking for, using LIKE '%arizona%' works perfectly fine.