pears.tidyaddresswithoutpostcode

CREATE PROCEDURE "pears"."tidyaddresswithoutpostcode"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "persid" CHAR(20) ) 
BEGIN
  UPDATE "person" SET "addr1" = "nullif"("trim"("addr1"),''),"addr2" = "nullif"("trim"("addr2"),''),"addr3" = "nullif"("trim"("addr3"),''),
    "town" = "nullif"("trim"("town"),''),"county" = "nullif"("trim"("county"),''),"country" = "nullif"("trim"("country"),''),"postcode" = "nullif"("trim"("postcode"),'') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr1" = "replace"("addr1",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr1" = "replace"("addr1",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr2" = "replace"("addr2",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr2" = "replace"("addr2",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr3" = "replace"("addr3",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "addr3" = "replace"("addr3",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "town" = "replace"("town",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "town" = "replace"("town",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "county" = "replace"("county",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "county" = "replace"("county",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "country" = "replace"("country",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "country" = "replace"("country",'  ',' ') WHERE "person"."personid" = "persid";
  UPDATE "person" SET "country" = "county","county" = NULL WHERE "personid" = "persid" AND "county" = any(SELECT "place" FROM "places" WHERE "code" = 'n');
  UPDATE "person" SET "country" = "town","town" = NULL WHERE "personid" = "persid" AND "town" = any(SELECT "place" FROM "places" WHERE "code" = 'n');
  UPDATE "person" SET "country" = "addr3","addr3" = NULL WHERE "personid" = "persid" AND "addr3" = any(SELECT "place" FROM "places" WHERE "code" = 'n');
  UPDATE "person" SET "country" = "addr2","addr2" = NULL WHERE "personid" = "persid" AND "addr2" = any(SELECT "place" FROM "places" WHERE "code" = 'n');
  UPDATE "person" SET "country" = "addr1","addr1" = NULL WHERE "personid" = "persid" AND "addr1" = any(SELECT "place" FROM "places" WHERE "code" = 'n');
  UPDATE "person" SET "county" = "country","country" = NULL WHERE "person"."personid" = "persid" AND "county" IS NULL AND "country" = any(SELECT "place" FROM "places" WHERE "code" = 'c');
  UPDATE "person" SET "county" = "town","town" = NULL WHERE "person"."personid" = "persid" AND "county" IS NULL AND "town" = any(SELECT "place" FROM "places" WHERE "code" = 'c');
  UPDATE "person" SET "county" = "addr3","addr3" = NULL WHERE "person"."personid" = "persid" AND "county" IS NULL AND "addr3" = any(SELECT "place" FROM "places" WHERE "code" = 'c');
  UPDATE "person" SET "county" = "addr2","addr2" = NULL WHERE "person"."personid" = "persid" AND "county" IS NULL AND "addr2" = any(SELECT "place" FROM "places" WHERE "code" = 'c');
  UPDATE "person" SET "county" = "addr1","addr1" = NULL WHERE "person"."personid" = "persid" AND "county" IS NULL AND "addr1" = any(SELECT "place" FROM "places" WHERE "code" = 'c');
  UPDATE "person" SET "town" = "county","county" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "county" = any(SELECT "place" FROM "places" WHERE "code" = 't');
  UPDATE "person" SET "addr1" = "string"("addr1",', ',"addr2"),"addr2" = "addr3","addr3" = "town","town" = "county","county" = NULL WHERE "person"."personid" = "persid"
    AND "town" IS NOT NULL AND "county" = any(SELECT "place" FROM "places" WHERE "code" = 't');
  UPDATE "person" SET "town" = "addr3","addr3" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "addr3" = any(SELECT "place" FROM "places" WHERE "code" = 't');
  UPDATE "person" SET "town" = "addr2","addr2" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "addr2" = any(SELECT "place" FROM "places" WHERE "code" = 't');
  UPDATE "person" SET "town" = "addr1","addr1" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "addr2" IS NULL AND "addr1" = any(SELECT "place" FROM "places" WHERE "code" = 't');
  UPDATE "person" SET "town" = "addr3","addr3" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "addr3" IS NOT NULL;
  UPDATE "person" SET "town" = "addr2","addr2" = NULL WHERE "person"."personid" = "persid" AND "town" IS NULL AND "addr2" IS NOT NULL
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."tidyaddresswithoutpostcode" IS 
{CREATE PROCEDURE tidyaddresswithoutpostcode 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN persid CHAR(20))
BEGIN
  UPDATE person SET addr1 = NULLIF(TRIM(addr1),''),addr2 = NULLIF(TRIM(addr2),''),addr3 = NULLIF(TRIM(addr3),''), 
 town = NULLIF(TRIM(town),''),county = NULLIF(TRIM(county),''),country = NULLIF(TRIM(country),''),postcode = NULLIF(TRIM(postcode),'') WHERE person.personid = persid;
  UPDATE person SET addr1 = REPLACE(addr1,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET addr1 = REPLACE(addr1,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET addr2 = REPLACE(addr2,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET addr2 = REPLACE(addr2,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET addr3 = REPLACE(addr3,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET addr3 = REPLACE(addr3,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET town = REPLACE(town,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET town = REPLACE(town,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET county = REPLACE(county,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET county = REPLACE(county,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET country = REPLACE(country,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET country = REPLACE(country,'  ',' ') WHERE person.personid = persid;
  UPDATE person SET country = county,county = NULL WHERE personid = persid AND county = any(SELECT place FROM places WHERE code = 'n');
  UPDATE person SET country = town,town = NULL WHERE personid = persid AND town = any(SELECT place FROM places WHERE code = 'n');
  UPDATE person SET country = addr3,addr3 = NULL WHERE personid = persid AND addr3 = any(SELECT place FROM places WHERE code = 'n');
  UPDATE person SET country = addr2,addr2 = NULL WHERE personid = persid AND addr2 = any(SELECT place FROM places WHERE code = 'n');
  UPDATE person SET country = addr1,addr1 = NULL WHERE personid = persid AND addr1 = any(SELECT place FROM places WHERE code = 'n');
  UPDATE person SET county = country,country = NULL WHERE person.personid = persid AND county IS NULL AND country = any(SELECT place FROM places WHERE code = 'c');
  UPDATE person SET county = town,town = NULL WHERE person.personid = persid AND county IS NULL AND town = any(SELECT place FROM places WHERE code = 'c');
  UPDATE person SET county = addr3,addr3 = NULL WHERE person.personid = persid AND county IS NULL AND addr3 = any(SELECT place FROM places WHERE code = 'c');
  UPDATE person SET county = addr2,addr2 = NULL WHERE person.personid = persid AND county IS NULL AND addr2 = any(SELECT place FROM places WHERE code = 'c');
  UPDATE person SET county = addr1,addr1 = NULL WHERE person.personid = persid AND county IS NULL AND addr1 = any(SELECT place FROM places WHERE code = 'c');
  UPDATE person SET town = county,county = NULL WHERE person.personid = persid AND town IS NULL AND county = any(SELECT place FROM places WHERE code = 't');
  UPDATE person SET addr1 = string(addr1,', ',addr2),addr2 = addr3,addr3 = town,town = county,county = NULL WHERE person.personid = persid 
 AND town IS NOT NULL AND county = any(SELECT place FROM places WHERE code = 't');
  UPDATE person SET town = addr3,addr3 = NULL WHERE person.personid = persid AND town IS NULL AND addr3 = any(SELECT place FROM places WHERE code = 't');
  UPDATE person SET town = addr2,addr2 = NULL WHERE person.personid = persid AND town IS NULL AND addr2 = any(SELECT place FROM places WHERE code = 't');
  UPDATE person SET town = addr1,addr1 = NULL WHERE person.personid = persid AND town IS NULL AND addr2 IS NULL AND addr1 = any(SELECT place FROM places WHERE code = 't');
  UPDATE person SET town = addr3,addr3 = NULL WHERE person.personid = persid AND town IS NULL AND addr3 IS NOT NULL;
  UPDATE person SET town = addr2,addr2 = NULL WHERE person.personid = persid AND town IS NULL AND addr2 IS NOT NULL
END
}