MYSQL filtering conditionally on first 2 characters

Question

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

Can you edit your question and add some simple data and the expected output — SelVazi, Mar 30 '23 at 10:54

score 1 · Accepted Answer · answered Mar 30 '23 at 11:00

1

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

answered Mar 30 '23 at 11:00

SelVazi

10,028
2
13
29

score 0 · Answer 2 · answered Apr 02 '23 at 08:00

Another response from my side.

with s1 as (select *,
replace(replace(replace(value,1,''),2,''),3,'') rp from series),
s2 as (select *,row_number() over (partition by rp) rn from s1)
select value from s2 where rn = 1

you can use multiple replace function based on your data set.

result

MYSQL filtering conditionally on first 2 characters

2 Answers2