parse_address — 获取一行地址并将其分成几个部分
record parse_address(
text address)
;
返回以地址作为输入,并返回由字段组成的记录输出 num , 街道 , 街道2 , 地址1 , 城市 , 状态 , 拉链 , Zipplus , 国家/地区 。
可用性:2.2.0
This method needs address_standardizer extension.
单地址
SELECT num, street, city, zip, zipplus FROM parse_address('1 Devonshire Place, Boston, MA 02109-1234') AS a;
num | street | city | zip | zipplus -----+------------------+--------+-------+--------- 1 | Devonshire Place | Boston | 02109 | 1234
地址表
-- basic table CREATE TABLE places(addid serial PRIMARY KEY, address text); INSERT INTO places(address) VALUES ('529 Main Street, Boston MA, 02129'), ('77 Massachusetts Avenue, Cambridge, MA 02139'), ('25 Wizard of Oz, Walaford, KS 99912323'), ('26 Capen Street, Medford, MA'), ('124 Mount Auburn St, Cambridge, Massachusetts 02138'), ('950 Main Street, Worcester, MA 01610'); -- parse the addresses -- if you want all fields you can use (a).* SELECT addid, (a).num, (a).street, (a).city, (a).state, (a).zip, (a).zipplus FROM (SELECT addid, parse_address(address) As a FROM places) AS p;
addid | num | street | city | state | zip | zipplus -------+-----+----------------------+-----------+-------+-------+--------- 1 | 529 | Main Street | Boston | MA | 02129 | 2 | 77 | Massachusetts Avenue | Cambridge | MA | 02139 | 3 | 25 | Wizard of Oz | Walaford | KS | 99912 | 323 4 | 26 | Capen Street | Medford | MA | | 5 | 124 | Mount Auburn St | Cambridge | MA | 02138 | 6 | 950 | Main Street | Worcester | MA | 01610 | (6 rows)