Question 20250 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL filtering conditionally on first 2 characters

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search