====== 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>
create function "pears"."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>