pears.FormatAddress

CREATE FUNCTION "pears"."FormatAddress"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "name" CHAR(100),IN "addr1" CHAR(50),IN "addr2" CHAR(50),IN "addr3" CHAR(50),IN "town" CHAR(50),IN "county" CHAR(50),IN "country" CHAR(50),IN "postcode" CHAR(50),
  IN "Capitalise" SMALLINT,IN "Style" CHAR(1) )  /* Set the seperator for block or line */ /* If capitalise option is set, capitalise town. */ /* Build Formatted Address */
RETURNS CHAR(300) BEGIN DECLARE "FormattedAddress" CHAR(300);DECLARE "Separator" CHAR(2);DECLARE "PrePostCodeSeparator" CHAR(2);SET "FormattedAddress" = '';
  IF "style" = 'B' THEN SET "Separator" = "char"(13)+"char"(10);
    SET "PrePostCodeSeparator" = "Separator"
  ELSE SET "Separator" = ', ';
    SET "PrePostCodeSeparator" = ' '
  END IF;
  IF "Capitalise" = 1 THEN SET "town" = "upper"("Town")
  END IF;
  IF "isnull"("name",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("name")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("addr1",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("addr1",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr1")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("addr2",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("addr2",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr2")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("addr3",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("addr3",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("addr3")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("town",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("town",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("town")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("county",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("county",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("county")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("country",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "Separator"
  END IF;
  IF "isnull"("country",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("country")
  END IF;
  IF "isnull"("FormattedAddress",'') <> '' AND "isnull"("postcode",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "PrePostCodeSeparator"
  END IF;
  IF "isnull"("postcode",'') <> '' THEN SET "FormattedAddress" = "FormattedAddress" || "trim"("postcode")
  END IF;
  RETURN "FormattedAddress"
END