pears.HMRC_CheckPostCode

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