pears.HMRC_CheckPostCode

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
}