I have a table of Outcodes (in the UK, the first part of postcodes/zipcode) for example
id,outcode
1, AB1
2, AB2... and so on
27, AL1
28, AL2...and so on
52, B1
52, B2
52, B3
61, BA1
etc.
I am trying to get a list of unique first parts of the outcode so
AB,
AL,
B,
BA1
The issue is that in the UK the first part of the postcode can be 1 OR 2 letters so AB = Aberdeen, B = Birmingham etc, then a number.
The table consists of id, outcode and some other arbitrary information.
I have managed to get the first part with this code if its 1 letter then a number:-
SELECT DISTINCT(SUBSTRING(outcode,1,1)) as outcode
FROM outcodepostcodes
WHERE SUBSTRING(outcode, 2, 1) * 1 > 0
and by changing the query to
SELECT DISTINCT(SUBSTRING(outcode,1,2)) as outcode
FROM outcodepostcodes
WHERE SUBSTRING(outcode, 2, 1) * 1 = 0
I get all the double letters, but i would like ideally is to have a single query to list both other wise I am going to have to do 2 queries and merge the arrays.
I'm pretty sure its an if statement somewhere but cant get it to work...
Thanks in advance
Keith
CASE WHEN clause can do the trick :
select DISTINCT(case when SUBSTRING(outcode, 2, 1) * 1 > 0 then SUBSTRING(outcode,1,1)
else SUBSTRING(outcode,1,2) end) as outcode
from outcodepostcodes