pears.ValidateNINumber [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.ValidateNINumber ======
<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"."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
}
</code>