I am trying to get the outer and prefix for uk postcodes i.e. NW3 6BX. A space divides the outer part of the post code for which extracting it can be easily achieved by doing:

SUBSTRING_INDEX(delivery_postcode, ' ', 1) AS outer_postcode

I would like to get also the prefix which is the the first one or two letters before the numbers. In this case NW. I would like to use the previous expression replacing the ' ' with any number. How to do this ?


  • Do you know that the first part (the outward code) has 6 different formats?: A9, A99, A9A, AA9, AA99, AA9A Mar 17 '16 at 10:05
  • yes I need the first 1 or 2 letters for the prefix. The outer postcode is the one left from the space as explained in the question
    – Blue Moon
    Mar 17 '16 at 10:07

As there are only two possible results (one or two characters before the digit) you can do a brute force approach using MySQL's limited regex support:

case when delivery_postcode rlike '^.[0-9]'         -- a digit as 2nd character?
     then substring(delivery_postcode from 1 for 1) -- first char
     else substring(delivery_postcode from 1 for 2) -- first two chars

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.