How make the query in SQL Server which compare the data in data base

In my database one field "Preferred Location" will be there, which contains the city name like "Ahmadabad,Anand,Baroda"

In employer section, when employer entered the location "Anand" 开发者_StackOverflowthen above record will be selected.

How can I make the query which search the all city with city Ahmadabad and when search then this recorde will be display

The correct answer is you need to normalize your SQL schema a bit. You need three separate tables: customer, locations, and customer_locations. The locations table has ONE record per city with at least two columns: city_id and name. The customer_locations table has exactly two columns: customer_id and city_id. This is known as a "lookup" table and allows you to define "many-to-many" relationships.

Now your query will be a little more complex because you'll have to use "join"s. But with complexity comes flexibility - your searches will be exact and you'll avoid problems such having records with accidentally misspelled city names (which ultimately wouldn't match).

I'm sure you'll have to do a little more research, but your query will look something like:

select cust.*, city.* from customers cust inner join customer_locations cl on
cust.customer_id = cl.customer_id inner join locations city on cl.city_id = city.city_id
where city.name = 'Anand';

The easiest to use 'LIKE'

For example:

select *
from MyTable
where Location like '%Ahmadabad%'

However, this will also find cities like 'NewAhmadabad'. Not sure if you can neglect.

You could do something like this, if you can't neglect:

select *
from MyTable
where 
    Location like '%,Ahmadabad,%' or 
    Location like 'Ahmadabad,%' or
    Location like '%,Ahmadabad'