How to extract numeric values from text data in MySQL query

Question

I have a table containing addresses. I would like to perform a query to select rows where the numeric values match.

address1        postcode
13 Some Road    SW1 1AA
House 5         G3 7L

e.g

select * from addresses where numeric(address1)=13 and numeric(postcode)=11

^^ That would match the first row

select * from addresses where numeric(address1)=5 and numeric(postcode)=37

^^ That would match the second row

Is this possible?

For your address1 field you could use LIKE but that won't help on your postcode where there's a space. What are you trying to accomplish? — Cfreak
– Cfreak, Commented Apr 2, 2015 at 14:39
@Cfreak thanks for the reply. Yes, and the address may be "15 house number, 3 some street" in which case I need the numeric value to be 153. I am trying to establish whether customer accounts match up against delivery addresses. Matching on the numeric aspect of the address plus postcode seems like a reasonably reliable way. Same way that credit card companies match billing addresses — Chris
– Chris, Commented Apr 2, 2015 at 14:46
The method seems unreliable. Certainly there are tons of addresses that would happen to share the same street number and same numbers of their post-code. In the US it's definitely true. My recommendation would be to check the entire address. — Cfreak
– Cfreak, Commented Apr 2, 2015 at 19:25
IF you are really intent on doing this, pull the numbers out of your address ahead of time and store them in separate columns. At least then the lookup for them will be MUCH faster. Using LIKE or REGEXP will require a full table scan. — Cfreak
– Cfreak, Commented Apr 2, 2015 at 19:27

Community · Accepted Answer · 2017-05-23 10:30:15Z

1

Yes, this is possible. You could write a function that uses a regular expression to replace all non-numeric characters in the field with the empty character so the result would be only numeric characters returned from that function, and then filter on that function.

You might be interested in this stackoverflow question and this blog post.

Comments

mysqlrockstar · Accepted Answer · 2015-04-02 14:52:55Z

0

select * from addresses where address1 REGEXP '(13)' and postcode REGEXP '(11)';

answered Apr 2, 2015 at 14:52

mysqlrockstar

2,6301 gold badge21 silver badges39 bronze badges

2 Comments

Chris Over a year ago

Thank you but this doesn't work. If the address is, for example, "House 15, On 10th Street" then this doesn't match 1510

mysqlrockstar Over a year ago

In that case use this WHERE address1 REGEXP '(15 | 10)' ; will work 100%

Collectives™ on Stack Overflow

How to extract numeric values from text data in MySQL query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related