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?
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,
address_standardizer from the PostGIS project and certainly better if you're only using United States addresses.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 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
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
