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 ?