CODEWITHSUNDEEP
sqldatabase
kiasy
kiasy

Reputation: 304

SQL Match City Name Inside Full Address?

How would you list the people from a database that are not from 'London'? Say the database is:

Cust_id         address
  1             33 avenue, Liverpool
  2             21 street 12345, London 
  3             469 connection ave, Manchester

I'd like to list the customers that are NOT from London. Here's what I've tried:

select Cust_id from customers where address <> 'London';

Now when I do that, it lists all the customers, regardless of location.

Help would be greatly appericated.

Upvotes: 1

Views: 574

Answers (2)

peter.petrov
peter.petrov

Reputation: 39457

Try this:

select Cust_id from customers where address not like '%London%';

or this:

select Cust_id from customers where not address like '%London%';

Both of these are OK.

For more details on LIKE see e.g. here: SQL LIKE

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300559

Not ideal but might satisfy your requirements:

 select Cust_id from customers 
 where address NOT LIKE '% London%';

[Note the added space: it assumes you will always precede the city name with a space. '%London%' would match words containing London]

(It might be better if you had a normalised address, i.e. broken into street address, town, city, etc.))

Upvotes: 2

Related Questions