-1

I'm working with an address database using PostgreSQL 9.5 and trying to create a select statement where only the names of the streets and not the types are returned. So if I have a record that says: 'Golf Course Rd', I only want to get back: 'Golf Course' or if the record is 'Bentwood Rd', I only want 'Bentwood' returned.

Any suggestions on how to tackle that?

  • 1
    Could you give us real test data, are you working with real addresses or you just want to clip off "Rd."? – Evan Carroll Mar 1 '17 at 20:39
  • I have voted to close this. I like my answer a lot but perhaps it's better elsewhere. Others do not read this question the same way and as is I can't prove them wrong. We need more information. – Evan Carroll Mar 2 '17 at 3:10
7

This is a question address canonicalization and parsing. Essentially what you're talking about is handled through a gazetteer (geographical rule set). There are two ways to do this right,

  1. address_standardizer from the PostGIS project and certainly better if you're only using United States addresses.
  2. pgsql-postal may be a better method for international addresses.

I'll show the address standardizer version for the address,

Chicken Ranch
10511 Homestead Rd
Pahrump, NV 89061

Using standardize_address from address_standardizer, returns a composite type of stdaddr

SELECT * FROM standardize_address('us_lex',
   'us_gaz', 'us_rules', '10511 Homestead Rd, Pahrump, NV 89061');
 building | house_num | predir | qual | pretype |   name    | suftype | sufdir | ruralroute | extra |  city   | state  | country | postcode | box | unit 
----------+-----------+--------+------+---------+-----------+---------+--------+------------+-------+---------+--------+---------+----------+-----+------
          | 10511     |        |      |         | HOMESTEAD | ROAD    |        |            |       | PAHRUMP | NEVADA | USA     | 89061    |     | 
(1 row)

So you can see, ROAD is pulled out in suftype

Based on this q/a

| improve this answer | |
3

Based on the content of your question, you want everything before the last space... right?

One way would be to use regular expressions:

rextester: http://rextester.com/YIZT9659

with foo as (
  select 'Fizz Buzz Rd'::text as foo
)
select regexp_replace(foo, '(.*)( .*?)', '\1') as StreetName
from foo as bar

returns: Fizz Buzz

| improve this answer | |
3

Sequence of characters (.*) followed by a space ().
The matched expression within the (first) brackets is being returned. https://www.postgresql.org/docs/current/static/functions-matching.html

select substring ('Fizz Buzz Rd' from '(.*) ')

Fizz Buzz

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.