Regex and GROUP from MySQL

Question

I have a database of companies addresses (street name) like :

876 Lexington ave
703 Lexington ave
872 Lexington ave
3501 Jack Northrop Ave
3503 Jack Northrop Ave
3505 Jack Northrop Ave

And so on.....

What I am trying to do is SELECT a row and GROUP them by addresses/street name to get the output like : Lexington ave, Jack Northrop Ave. Goal is to erase the numeric street number and then GROUP them by name.

How can I achieve this with MySQL query? Any help will be highly appreciated.

Thanks

score 0 · Answer 1 · answered Aug 19 '16 at 03:54

0

You can remove leading numbers using trim(). So this might do what you want:

select distinct trim(leading '0123456789 ' from address) as street_name
from t;

answered Aug 19 '16 at 03:54

Gordon Linoff

1,242,037
58
646
786

Can we use pattern for LEADING? I guess it was for string only. By the way I tired that query, didn't worked. Thank you though :) – Akam Hasan Sharkar Aug 19 '16 at 04:14
1

I don't think this will work, you need to remove one letter at a time like with this: http://stackoverflow.com/questions/11431831/remove-all-numeric-characters-from-column-mysql, but of course if there's a 4th Avenue or something it would break it – tomb Aug 19 '16 at 04:15

score 0 · Accepted Answer · answered Aug 19 '16 at 04:47

If you always want to remove the characters prior to the first space, you can use substr and instr:

select distinct substr(address, instr(address, ' ') + 1)
from yourtable;

SQL Fiddle Demo

If there are some street addresses that don't include this prefix, here's another option using union all and regexp to combine the results:

select distinct address 
from (
  select substr(address, instr(address, ' ') + 1) as address
  from yourtable
  where address regexp '^[0-9]'
  union all
  select address
  from yourtable
  where address not regexp '^[0-9]'
  ) t

More Fiddle

Regex and GROUP from MySQL

2 Answers2