Code:
SELECT * FROM contacts WHERE postcode = 'MK3 6ZZ';
will match any records with a single postcode in Milton Keynes.
Code:
SELECT * FROM contacts WHERE postcode LIKE 'MK3 %';
will match records with the postcode area MK3 in Milton Keynes. Care needs to be taken when using LIKE. Notice the space after the '3'.
Code:
SELECT * FROM contacts WHERE postcode LIKE 'MK3%';
is not the same as the above. Notice that there is no space after the '3'. The would return records, not only in the MK3 postcode area but also, MK31 to MK39 (if they existed).
Code:
SELECT * FROM contacts WHERE postcode REGEXP ('^E[1-9]');
will match any postcodes in the E1 to E9 (London) postcode areas.
As I will show, you can mix these examples to search for more than a single postcode area in one query. Useful if you want the query to return a larger geographical area.
If you want to target members/customers/recipients within a geographical location then here are a few snippets I have gleaned over the years.
Code:
-- London
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]'));
-- Scotland
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('KW','IV','AB','PH','DD','PA','FK','KY','ML','KA','EH','DG','TD')
OR postcode REGEXP ('^G[1-9]'));
-- England
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'));
-- Wales
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('LL','SY','SA','LD','SA','CF','NP'));
-- Northern Ireland
SELECT COUNT(*)
FROM members
WHERE postcode LIKE 'BT%';
-- South East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BN','CM','CT','GU','HP','LU','ME','MK','PO','RG','RH','SG','SL','SO','SS','TN'));
-- East Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CB','CO','DE','DN','IP','LE','LN','NG','NR','PE')
OR postcode REGEXP ('^S[1-9]'));
-- West Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CV','DY','HR','NN','ST','TF','WR','WS','WV')
OR postcode REGEXP ('^B[1-9]'));
-- North East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('DH','DL','HG','HU','LS','NE','SR','TS','WF','YO'));
-- North West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA','WN')
OR postcode REGEXP ('^L[1-9]%'
OR postcode REGEXP ('^M[1-9]%'));
-- South West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX'));
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]'));
-- Scotland
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('KW','IV','AB','PH','DD','PA','FK','KY','ML','KA','EH','DG','TD')
OR postcode REGEXP ('^G[1-9]'));
-- England
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN
('BR','CR','DA','EC','EN','HA','IG','KT','NW','RM','SE','SM','SW','TW','UB','WC','WD','BN','CM','CT','GU','HP','LU','ME','MK',
'PO','RG','RH','SG','SL','SO','SS','TN','CB','CO','DE','DN','IP','LE','LN','NG','NR','PE','CV','DY','HR','NN','ST','TF','WR','WS',
'WV','DH','DL','HG','HU','LS','NE','SR','TS','WF','YO','BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA',
'WN','BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX')
OR postcode REGEXP ('^E[1-9]')
OR postcode REGEXP ('^N[1-9]')
OR postcode REGEXP ('^W[1-9]')
OR postcode REGEXP ('^S[1-9]')
OR postcode REGEXP ('^B[1-9]')
OR postcode REGEXP ('^L[1-9]')
OR postcode REGEXP ('^M[1-9]'));
-- Wales
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('LL','SY','SA','LD','SA','CF','NP'));
-- Northern Ireland
SELECT COUNT(*)
FROM members
WHERE postcode LIKE 'BT%';
-- South East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BN','CM','CT','GU','HP','LU','ME','MK','PO','RG','RH','SG','SL','SO','SS','TN'));
-- East Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CB','CO','DE','DN','IP','LE','LN','NG','NR','PE')
OR postcode REGEXP ('^S[1-9]'));
-- West Midlands
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('CV','DY','HR','NN','ST','TF','WR','WS','WV')
OR postcode REGEXP ('^B[1-9]'));
-- North East
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('DH','DL','HG','HU','LS','NE','SR','TS','WF','YO'));
-- North West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BB','BD','BL','CA','CH','CW','FY','HD','HX','LA','OL','PR','SK','WA','WN')
OR postcode REGEXP ('^L[1-9]%'
OR postcode REGEXP ('^M[1-9]%'));
-- South West
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,2) IN ('BA','BH','BS','DT','EX','GL','AL','PL','SN','SP','TA','TQ','TR','OX'));
I do use LIKE for small queries but REGEXP (Regular Expression) is better to avoid any ambiguity. LIKE 'MK1%' would be a mistake because it would select not only MK1 but, MK11, MK12, etc.
Say that you want to target UK members/customers/recipients within a radius of a postcode, how do you work it out? Well, there are websites that can help. Do a search for something like, 'Find UK Postcodes Inside a Radius' and you will find a few. Type in a postcode for the center of the search radio and then the mile/kilometers radius and then you can feed the results into an SQL query.
Example:
Code:
SELECT COUNT(*)
FROM members
WHERE (SUBSTRING(postcode,1,4) IN ('MK1 ','MK17','MK2 ','MK3 ','MK4 ','MK5 ');
FROM members
WHERE (SUBSTRING(postcode,1,4) IN ('MK1 ','MK17','MK2 ','MK3 ','MK4 ','MK5 ');
