====== pears.HMRC_CheckPostCode ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."HMRC_CheckPostCode" IS 
{create FUNCTION HMRC_CheckPostCode(in @addr1 varchar(100))
    returns  char(100)
    begin
    
      declare @postcode char(30);
      declare @pos1 smallint;
      declare @pos2 smallint;
      declare @pos3 smallint;
      declare @pos4 smallint;
      declare @pos5 smallint;
      declare @pos6 smallint;
      declare @pos smallint;
      declare @length smallint;
    
      set @pos1=patindex('%[A-Z][0-9] [0-9][A-Z][A-Z]%',@addr1);
      set @pos2=patindex('%[A-Z][0-9][0-9] [0-9][A-Z][A-Z]%',@addr1);
      set @pos3=patindex('%[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]%',@addr1);
      set @pos4=patindex('%[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]%',@addr1);
      set @pos5=patindex('%[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]%',@addr1);
      set @pos6=patindex('%[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]%',@addr1);
      case
      when @pos6 > 0 then set @pos=@pos6;
        set @length=7
      when @pos5 > 0 then set @pos=@pos5;
        set @length=8
      when @pos4 > 0 then set @pos=@pos4;
        set @length=8
      when @pos3 > 0 then set @pos=@pos3;
        set @length=7
      when @pos2 > 0 then set @pos=@pos2;
        set @length=7
      when @pos1 > 0 then set @pos=@pos1;
        set @length=6
      end case
      ;
      if @pos > 0 then
         
        set @postcode=substr(@addr1,@pos,@length);
        return @postcode;
      else return ''
    
      end if
    end
}
</code>