From a10d65c8040afbaa527ab930802222663d5be87e Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 8 Mar 2013 13:20:47 -0500 Subject: [PATCH] when parsing state from city, anchor pattern on right This avoids "ANCHOR POINT AK" from becoming {"ANCHORINT", "AK"} Signed-off-by: Galen Charlton --- sql/base/base.sql | 6 +++--- 1 files changed, 3 insertions(+), 3 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index e18c004..751ce72 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -365,9 +365,9 @@ CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2'); city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1'); ELSE - IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN - state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' ); - city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' ); + IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN + state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' ); + city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' ); ELSE IF city_state_zip ~ E'^\\S+$' THEN city := city_state_zip; -- 1.7.2.5