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, Apr 02 '15 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, Apr 02 '15 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, Apr 02 '15 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, Apr 02 '15 at 19:27

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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.

See also mysql-udf-regexp.

edited May 23 '17 at 10:30

Community

1
1

answered Apr 02 '15 at 14:58

axiopisty

4,972
8
44
73

score 0 · Answer 2 · answered Apr 02 '15 at 14:52

0

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

answered Apr 02 '15 at 14:52

mysqlrockstar

2,536
1
19
36

Thank you but this doesn't work. If the address is, for example, "House 15, On 10th Street" then this doesn't match 1510 – Chris Apr 02 '15 at 15:42
In that case use this `WHERE address1 REGEXP '(15 | 10)' ;` will work 100% – mysqlrockstar Apr 02 '15 at 16:41

How to extract numeric values from text data in MySQL query

2 Answers2