How to detect addresses with no number

Question

I have a column address with thousands of addresses, most of them have a number but lot of addresses have no number, the addresses are like this:

- streetname 4
- streetname 8
- streetname 4/5
- streetname

how do i detect the fields like 'streetname' having no number. I have tried with:

REGEXP '^-?[0-9]+$';

or isANumber() but none gives the right result

Is that your sample data? There's a lot of ways a street address can be expressed, the "streetname ##" is just one of them. — tadman, Aug 04 '16 at 19:48
If what you're ultimately trying to do is to parse an address, and this is not a learning exercise like a homework assignment, I would **strongly** suggest that you go with an existing solution like the [Google Maps API](https://developers.google.com/maps/). See [this question](http://stackoverflow.com/questions/518210/where-is-a-good-address-parser) for more details. There are so many different ways that addresses are articulated, that you're going to spend the rest of your career programming around every edge case you encounter. — Bob Kaufman, Aug 04 '16 at 19:53
@BobKaufman You're not supposed to tell him that now... wait till the end of his career to point it out. — Hogan, Aug 04 '16 at 19:55

score 3 · Accepted Answer · answered Aug 04 '16 at 19:50

3

You could use:

address rlike '^[^0-9]*$'

answered Aug 04 '16 at 19:50

Gordon Linoff

thank you @Gordon Linoff this give the wished result – papacico Aug 04 '16 at 19:56

Hogan · Answer 2 · 2016-08-04T19:51:50.687

1

The following will remove the number from the end

TRIM(TRAILING '1234567890 ' FROM fieldname)

SO

WHERE TRIM(TRAILING '1234567890 ' FROM fieldname) <> TRIM(TRAILING ' ' FROM fieldname)

have numbers and

WHERE TRIM(TRAILING '1234567890 ' FROM fieldname) = TRIM(TRAILING ' ' FROM fieldname)

don't.

edited Aug 04 '16 at 19:51

answered Aug 04 '16 at 19:50

Hogan

This would assume that the OP is looking for numbers only at the end of the address. If so, it is a clever solution. – Gordon Linoff Aug 04 '16 at 19:51
Thanks @GordonLinoff - I thought that was what the question asked. – Hogan Aug 04 '16 at 19:52

ScaisEdge · Answer 3 · 2016-08-04T19:56:01.930

1

If you wnat select the rows that contain number

SELECT * FROM table WHERE your_column REGEXP '[0-9]';

or if you wnat the rows that not contain

SELECT * FROM table WHERE your_column NOT REGEXP '[0-9]';

edited Aug 04 '16 at 19:56

answered Aug 04 '16 at 19:50

ScaisEdge

@GordonLinoff yes..correct i have update the answer – ScaisEdge Aug 04 '16 at 19:56

3 Answers3