0

If I have a Postcode EH1 2GB I am looking for a function which will pull only the EH

Ideally this would work for postcodes which may only contain 1 leading letter such as G2 1AB and 2 numbers after the leading letters such as EH54 1BD and G25 2RD

I have tried using

I have tried pulling pulling the whole postcode and then using javascript (const extractPostalArea = (postalCode) => postalCode.match(/^[A-Z]+/)?.[0] ?? null;) to further separate the Postcode, however this is causing more issues than it is solving.

Is there a way to pull the postcode that would give a result similar to the below table...

Postcode PostalArea
B2 1GH B
EH2 4ED EH
SL4 5FR SL
E2W 4ST E

Any help would be much appreciated

I am using MariaDB

4
  • I have been using SELECT Company, Address, Town, Postcode From CollectionAddress which has been working fine. When I add REGEXP_SUBSTR(Postcode,'[A-Z]+') AS PostalArea to make SELECT Company, Address, Town, Postcode, REGEXP_SUBSTR(Postcode,'[A-Z]+') AS PostalArea From CollectionAddress it tells me that my query is syntactically wrong
    – PaulMcF87
    Commented Mar 2, 2023 at 18:06
  • Please provide the error message -- "near" tells us exactly where to look.
    – Rick James
    Commented Mar 2, 2023 at 18:34
  • @RickJames I got home and retyped it out and it seems to be working now
    – PaulMcF87
    Commented Mar 2, 2023 at 19:01
  • @PaulMcF87 Are you running the same version of MariaDB at home and wherever it didn't work? Commented Mar 2, 2023 at 19:40

1 Answer 1

2

You should be able to use the same regex in MariaDB as you did in the JavaScript by using the REGEXP_SUBSTR function, like:

SELECT REGEXP_SUBSTR(Postcode, '^[A-Z]+') AS PostalArea From CollectionAddress

The ^ ensures that the one-or-more [A-Z] has to be at the start of the string.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.