Useful Postcode Sector Code Snippet

Thanks to a work mate for this one:

In the UK our postcodes are formatted in quite a neat way.

We often have the need to aggregate up data to different geographic area as the following code makes this simple!

Snippet:UPDATE <Table>
SET postcode_sector = (LEFT(Postcode, 1) -- First character will always be a char
                                       + CASE ISNUMERIC(SUBSTRING(Postcode, 2,1)) -- Check 2nd character to see if its numeric
                                                      WHEN 1 THEN '' -- If its numeric add nothing
                                                       ELSE SUBSTRING(Postcode, 2,1) -- Otherwise add the second character
                                           END)

This then allows you to aggregate data to the following levels:

Region	Description	Postcode Sector
EAST MIDLANDS	Cambridge	CB
EAST MIDLANDS	Colchester	CO
EAST MIDLANDS	Derby	DE
EAST MIDLANDS	Doncaster	DN
EAST MIDLANDS	Ipswich	IP
EAST MIDLANDS	Leicester	LE
EAST MIDLANDS	Lincoln	LN
EAST MIDLANDS	Norwich	NR
EAST MIDLANDS	Nottingham	NG
EAST MIDLANDS	Peterborough	PE
EAST MIDLANDS	Sheffield	S
GREATER LONDON	Bromley	BR
GREATER LONDON	Croydon	CR
GREATER LONDON	Dartford	DA
GREATER LONDON	Enfield	EN
GREATER LONDON	Harrow	HA
GREATER LONDON	Ilford	IG
GREATER LONDON	Kingston upon Thames	KT
GREATER LONDON	London E	E
GREATER LONDON	London EC	EC
GREATER LONDON	London N	N
GREATER LONDON	London NW	NW
GREATER LONDON	London SE	SE
GREATER LONDON	London SW	SW
GREATER LONDON	London W	W
GREATER LONDON	London WC	WC
GREATER LONDON	Romford	RM
GREATER LONDON	Southall	UB
GREATER LONDON	Sutton	SM
GREATER LONDON	Twickenham	TW
GREATER LONDON	Watford	WD
NORTH EAST	Cleveland	TS
NORTH EAST	Darlington	DL
NORTH EAST	Durham	DH
NORTH EAST	Harrogate	HG
NORTH EAST	Hull	HU
NORTH EAST	Leeds	LS
NORTH EAST	Newcastle	NE
NORTH EAST	Sunderland	SR
NORTH EAST	Wakefield	WF
NORTH EAST	York	YO
NORTH WEST	Blackburn	BB
NORTH WEST	Blackpool	FY
NORTH WEST	Bolton	BL
NORTH WEST	Bradford	BD
NORTH WEST	Carlisle	CA
NORTH WEST	Chester	CH
NORTH WEST	Crewe	CW
NORTH WEST	Halifax	HX
NORTH WEST	Huddersfield	HD
NORTH WEST	Lancaster	LA
NORTH WEST	Liverpool	L
NORTH WEST	Manchester	M
NORTH WEST	Oldham	OL
NORTH WEST	Preston	PR
NORTH WEST	Stockport	SK
NORTH WEST	Warrington	WA
NORTH WEST	Wigan	WN
NORTHERN IRELAND	Belfast	BT
SCOTLAND	Aberdeen	AB
SCOTLAND	Dumfries	DG
SCOTLAND	Dundee	DD
SCOTLAND	Edinburgh	EH
SCOTLAND	Falkirk	FK
SCOTLAND	Galashiels	TD
SCOTLAND	Glasgow	G
SCOTLAND	Inverness	IV
SCOTLAND	Kilmarnock	KA
SCOTLAND	Kirkaldy	KY
SCOTLAND	Kirkwall	KW
SCOTLAND	Motherwell	ML
SCOTLAND	Paisley	PA
SCOTLAND	Perth	PH
SOUTH EAST	Brighton	BN
SOUTH EAST	Canterbury	CT
SOUTH EAST	Chelmsford	CM
SOUTH EAST	Guilford	GU
SOUTH EAST	Hemel Hempstead	HP
SOUTH EAST	Luton	LU
SOUTH EAST	Medway	ME
SOUTH EAST	Milton Keynes	MK
SOUTH EAST	Oxford	OX
SOUTH EAST	Portsmouth	PO
SOUTH EAST	Reading	RG
SOUTH EAST	Redhill	RH
SOUTH EAST	Slough	SL
SOUTH EAST	Southampton	SO
SOUTH EAST	Southend on Sea	SS
SOUTH EAST	St. Albans	AL
SOUTH EAST	Stevenage	SG
SOUTH EAST	Tonbridge	TN
SOUTH WEST	Bath	BA
SOUTH WEST	Bournemouth	BH
SOUTH WEST	Bristol	BS
SOUTH WEST	Dorchester	DT
SOUTH WEST	Exeter	EX
SOUTH WEST	Gloucester	GL
SOUTH WEST	Plymouth	PL
SOUTH WEST	Salisbury	SP
SOUTH WEST	Swindon	SN
SOUTH WEST	Taunton	TA
SOUTH WEST	Torquay	TQ
SOUTH WEST	Truro	TR
UNKNOWN	UNKNOWN	NULL
WALES	Cardiff	CF
WALES	Llandrindod	LD
WALES	Llandudno	LL
WALES	Newport	NP
WALES	Shrewsbury	SY
WALES	Swansea	SA
WEST MIDLANDS	Birmingham	B
WEST MIDLANDS	Coventry	CV
WEST MIDLANDS	Dudley	DY
WEST MIDLANDS	Hereford	HR
WEST MIDLANDS	Northampton	NN
WEST MIDLANDS	Stoke on Trent	ST
WEST MIDLANDS	Telford	TF
WEST MIDLANDS	Walsall	WS
WEST MIDLANDS	Wolverhampton	WV
WEST MIDLANDS	Worcester	WR

Hope its of some use!