MySQL Extract Postcode From Address Field

Question

I have an address column in my table that stores addresses in the following format Street Name, Town Name, Postcode. I know that I can use the explode function in PHP and get the postcode based on the commas. My question is, is there a way to extract the postcode using through a query?

Edit your question and provide sample data so we can see what the `address` looks like. — Gordon Linoff, Jan 29 '18 at 11:28
Possible duplicate of [Last index of a given substring in MySQL](https://stackoverflow.com/questions/12775352/last-index-of-a-given-substring-in-mysql) — Kobi, Jan 29 '18 at 11:30

score 4 · Accepted Answer · answered Jan 29 '18 at 11:29

4

If your components are separated by commas an always have a postcode, you can use substring_index():

select substring_index(address, ',', -1) as postcode

answered Jan 29 '18 at 11:29

Gordon Linoff

1,242,037
58
646
786

1

Beautiful! Lovely little snippet – Othyn Feb 20 '20 at 16:23

MySQL Extract Postcode From Address Field

1 Answers1