mysql select everything before a number 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 ?

Thanks,
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
end