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

What are you trying to achieve? Please provide examples. Perhaps, have a look at What is the correct syntax for a Regex find-and-replace using REGEXP_REPLACE in MariaDB?. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Sep 11, 2015 at 10:07
this post might help stackoverflow.com/questions/21378193/… — pcantalupo
– pcantalupo, Commented Sep 11, 2015 at 12:55
What's the content of the addresses column? can you post some sample data? It might be just a SUBSTRING(addresses, 0, 2). — solarc
– solarc, Commented Jun 12, 2018 at 22:18

rwitzel · Accepted Answer · 2018-11-17 15:06:23Z

1

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

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

answered Nov 17, 2018 at 15:06

rwitzel

1,86020 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1