Get list of address dont have UK address mysql

Question

I have a MySQL table contains a list of UK address, I was trying to get the list of address doesn't have a postcode.

I the list, we can see some of them don't have postcode at the end.

I was written a query as follows and didn't get the expected result.

select * from property_address WHERE property_address
REGEXP '^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$'

How to fix this query get working?

Strip last 2 tokens (use SUBSTRING_INDEX) and test does they match to the postcode pattern. — Akina, Aug 28 '20 at 09:22
The REGEX i have written seems to be correct. Bu it need to apply the last portion of the string — Shijin TR, Aug 28 '20 at 09:25
Not possible unless you know all postcode patterns for example G1 and G1 1BX are both Glasgow and do not match the pattern Ann nAA and even then it's difficult. — P.Salmon, Aug 28 '20 at 09:28
@ShijinTR: don't you just want to remove the leading `^` from the regex, so it matches at the end of the string instead of the whole string? — GMB, Aug 28 '20 at 09:32
UK postcode patterns are described, for example, [there](https://ideal-postcodes.co.uk/guides/uk-postcode-format). 6 different patterns are shown. — Akina, Aug 28 '20 at 09:33

score 1 · Accepted Answer · answered Aug 30 '20 at 00:49

1

I will assume the postcode regexp is correct.

    REGEXP '^([A-PR-UWYZ0-9]...|GIR 0AA)$'
    ______  _

You need to remove the "anchor" (^) that I underlined above. It is not "not". Instead, negate thus:

NOT REGEXP  '([A-PR-UWYZ0-9]...|GIR 0AA)$'
___         _

Akina's suggestion of first extracting via SUBSTRING_INDEX is likely to make the query faster.

answered Aug 30 '20 at 00:49

Rick James

135,179
13
127
222

But we don't know the exact length of the string – Shijin TR Aug 31 '20 at 04:56
@ShijinTR - If you know the number of spaces, then you can use `SUBSTRING_INDEX(... ' ', -2)` to get the last 2 words. – Rick James Aug 31 '20 at 05:02

Get list of address dont have UK address mysql

1 Answers1