pears.ValidateNINumber

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ValidateNINumber" IS 
{CREATE FUNCTION ValidateNINumber( IN @NI CHAR(10) ) 
RETURNS CHAR(250)
BEGIN
  // based ON IR Quality Standard Validation Specification FROM April 2005 Version 2.0
  // Sep 2020 HMRC Rules advice
  //  The characters D, F, I, Q, U, AND V are NOT used AS either the FIRST OR SECOND letter OF a NINO prefix.
  //  The letter O IS NOT used AS the SECOND letter OF a prefix.
  //  Prefixes BG, GB, KN, NK, NT, TN AND ZZ are NOT TO be used
  DECLARE "ResultMessage" CHAR(250);
  SET "ResultMessage" = '';
  IF @NI IS NULL THEN RETURN NULL
  END IF;
  IF "length"(@NI) <> 9 THEN SET "ResultMessage" = "ResultMessage"+'NI number is not 9 characters long. '
  END IF;
  IF "left"(@NI,2) NOT IN( 'AA','AB','AE','AG','AH','AK','AL','AM','AP','AR','AS','AT','AW','AX','AY','AZ','BA','BB','BE','BH','BK','BL','BM','BT','CA','CB','CE','CH','CK','CL','CR','EA','EB','EE','EH','EK','EL','EM','EP','ER','ES','ET','EW','EX','EY','EZ','GY','HA','HB','HE','HH','HK','HL','HN','HM','HP','HR','HS','HT','HW','HX','HY','HZ','JA','JB','JC','JE','JG','JH','JJ','JK','JL','JM','JN','JP','JR','JS','JT','JW','JX','JY','JZ','KA','KB','KC','KE','KH','KK','KL','KM','KP','KR','KS','KT','KW','KX','KY','KZ','LA','LB','LE','LH','LK','LL','LM','LP','LR','LS','LT','LW','LX','LY','LZ','MA','MW','MX','NA','NB','NE','NH','NJ','NL','NM','NP','NR','NS','NW','NX','NY','NZ','OA','OB','OE','OH','OK','OL','OM','OP','OR','OS','OX','PA','PB','PC','PE','PG','PH','PJ','PK','PW','PX','PY','PL','PM','PN','PP','PR','PS','PT','RA','RB','RE','RH','RK','RM','RP','RR','RS','RT','RW','RX','RY','RZ','SA','SB','SC','SE','SM','SN','SW','SG','SH','SJ','SK','SL','SP','SR','SS','ST','SX','SY','SZ','TA','TB','TE','TH','TJ','TK','TL','TM','TP','TR','TS','TT','TW','TX','TY','TZ','WA','WB','WE','WG','WK','WL','WM','WP','YA','YB','YE','YH','YK','YL','YM','YP','YR','YS','YT','YW','YX','YY','YZ','ZA','ZB','ZE','ZH','ZK','ZL','ZM','ZP','ZR','ZS','ZT','ZW','ZX','ZY' ) THEN
    SET "ResultMessage" = "ResultMessage"+'NI 2 character prefix used is not a valid prefix. '
  END IF;
  IF "left"(@NI,2) = 'TN' THEN
    SET "ResultMessage" = "ResultMessage"+'Temporary NI numbers are no longer valid, date of birth and gender should be completed instead. '
  END IF;
  IF "substr"(@NI,3,6) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]' THEN
    SET "ResultMessage" = "ResultMessage"+'Characters 3-8 of the NI number must be numeric. '
  END IF;
  IF "substr"(@NI,9,1) NOT LIKE '[A-D ]' THEN
    SET "ResultMessage" = "ResultMessage"+'The last character of the NI number must be A,B,C,D or a space. '
  END IF;
  IF "ResultMessage" = '' THEN SET "ResultMessage" = NULL
  END IF;
  RETURN "trim"("ResultMessage")
END
}