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?](http://stackoverflow.com/questions/27498929/what-is-the-correct-syntax-for-a-regex-find-and-replace-using-regexp-replace-in). — Wiktor Stribiżew, Sep 11 '15 at 10:07
this post might help http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function — pcantalupo, Sep 11 '15 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, Jun 12 '18 at 22:18

score 1 · Answer 1 · answered Nov 17 '18 at 15:06

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 '18 at 15:06

rwitzel

1 Answers1