regex capture group in mysql

Question

Is it possible to capture the regex match in the select part of the query in mysql?

I'd like to query for an initial letters in the UK postcode like:

SELECT all initiall letters from a post code (one or two) FROM addresses;

UK postcodes start with one or two letters and then have one or two digits, optional space, then two letters and finally a digit.

Examples:

SW8 4EX

E1 7AG

EC1 8AG

SE17 9AW

Answer 1

SELECT REGEXP_REPLACE('SW8 4EX', ' .*', '');

This should work with your examples. First make sure the postcode column does not contain leading space.