pears.PostCodePart

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."PostCodePart"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "postcode" CHAR(20),IN "part" CHAR(20) ) 
RETURNS CHAR(20)
deterministic
BEGIN
  DECLARE "i" INTEGER;
  DECLARE "area" CHAR(20);
  SET "postcode" = "trim"("postcode");
  SET "i" = "patindex"('%[0-9]%',"postcode");
  IF "i" = 0 THEN
    RETURN NULL
  END IF;
  SET "area" = "substr"("postcode",1,"i"-1);
  IF "part" = 'area' THEN
    RETURN "area"
  END IF;
  SET "postcode" = "stuff"("postcode",1,"i"-1,NULL);
  SET "i" = "patindex"('%[^0-9]%',"postcode");
  IF "part" = 'district' THEN
    RETURN "area"+"left"("substr"("postcode",1,"i"-1),2)
  END IF
END