pears.PhoneUpdate

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."PhoneUpdate" IS 
{CREATE FUNCTION pears.PhoneUpdate(IN NUMBER CHAR(50))
RETURNS CHAR(50)
BEGIN
  DECLARE WorkNum CHAR(50);
  //// Tidy up the NUMBER
  SET WorkNum=replacestr(TRIM(NUMBER),'-','',0);
  SET WorkNum=replacestr(WorkNum,' ','',0);
  SET WorkNum=replacestr(WorkNum,'(','',0);
  SET WorkNum=replacestr(WorkNum,')','',0);
  //// CHECK now ALL numbers
  IF patindex('%[^0-9]%',WorkNum) <> 0 THEN RETURN(NUMBER)
  END IF;
  ////
  //// CHECK FOR London (4 CHAR codes)
  CASE "left"(WorkNum,4) WHEN '0171' THEN
    SET WorkNum='020 7'+substr(WorkNum,5,3)+' '+substr(WorkNum,8,43);
    RETURN(WorkNum) WHEN '0181' THEN
    SET WorkNum='020 8'+substr(WorkNum,5,3)+' '+substr(WorkNum,8,43);
    RETURN(WorkNum)
  END CASE
  ;
  ////
  //// CHECK Martinstown, Carrickmore & Limavady (Possible confusion WITH Ballymena, Omagh & Londonderry below)
  CASE "left"(WorkNum,6) WHEN '012667' THEN //*Martinstown*//
    SET WorkNum='028 217'+substr(WorkNum,7,1)+' '+substr(WorkNum,8,46);
    RETURN(WorkNum) WHEN '015047' THEN //*Limavady*//
    SET WorkNum='028 777'+substr(WorkNum,7,1)+' '+substr(WorkNum,8,46);
    RETURN(WorkNum) WHEN '016627' THEN //*Carrickmore*//
    SET WorkNum='028 807'+substr(WorkNum,7,1)+' '+substr(WorkNum,8,46);
    RETURN(WorkNum)
  END CASE
  ; ////
  //// CHECK FOR other UK Cities (5 CHAR STD codes)
  CASE "left"(WorkNum,5) WHEN '01222' THEN //*Cardiff*//
    SET WorkNum='029 20'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01203' THEN //*Coventry*//
    SET WorkNum='024 76'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01705' THEN //*Portsmouth*//
    SET WorkNum='023 92'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01703' THEN //*Southampton*//
    SET WorkNum='023 80'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01232' THEN //*Belfast*//
    SET WorkNum='028 90'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01266' THEN //*Ballymena - check 5 or 6 digit local number*//
    IF LENGTH(WorkNum) = 10 THEN
      SET WorkNum='028 256'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46)
    ELSE SET WorkNum='028 25'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45)
    END IF;
    RETURN(WorkNum) WHEN '01754' THEN //*Larne*//
    SET WorkNum='028 28'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01504' THEN //*Londonderry*//
    SET WorkNum='028 71'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01265' THEN //*Colraine - check 5 or 6 digit local number*//
    IF LENGTH(WorkNum) = 10 THEN
      SET WorkNum='028 703'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46)
    ELSE SET WorkNum='028 70'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45)
    END IF;
    RETURN(WorkNum) WHEN '01648' THEN //*Magherafelt - check 5 or 6 digit local number*//
    IF LENGTH(WorkNum) = 10 THEN
      SET WorkNum='028 796'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46)
    ELSE SET WorkNum='028 79'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45)
    END IF;
    RETURN(WorkNum) WHEN '01247' THEN //*Bangor*//
    SET WorkNum='028 91'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01846' THEN //*Lisburn*//
    SET WorkNum='028 92'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01960' THEN //*Ballyclare*//
    SET WorkNum='028 93'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01849' THEN //*Antrim*//
    SET WorkNum='028 94'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01238' THEN //*Saintfield*//
    SET WorkNum='028 97'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01662' THEN //*Omagh*//
    SET WorkNum='028 82'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01868' THEN //*Dungannon*//
    SET WorkNum='028 87'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01396' THEN //*Downpatrick*//
    SET WorkNum='028 44'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01693' THEN //*Newry - check 5 or 6 digit local number*//
    IF LENGTH(WorkNum) = 10 THEN
      SET WorkNum='028 302'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46)
    ELSE SET WorkNum='028 30'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45)
    END IF;
    RETURN(WorkNum) WHEN '01861' THEN //*Armagh*//
    SET WorkNum='028 37'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01762' THEN //*Portadown*//
    SET WorkNum='028 38'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum) WHEN '01365' THEN //*Enniskillen*//
    SET WorkNum='028 66'+substr(WorkNum,6,2)+' '+substr(WorkNum,8,45);
    RETURN(WorkNum)
  END CASE
  ;
  //// CHECK FOR other UK Cities (6 CHAR STD codes)
  CASE "left"(WorkNum,6) WHEN '012657' THEN //*Ballycastle*//
    SET WorkNum='028 207'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '012656' THEN //*Ballymoney*//
    SET WorkNum='028 276'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '012665' THEN //*Kilrea*//
    SET WorkNum='028 295'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '016626' THEN //*Newtonstewart*//
    SET WorkNum='028 816'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '016625' THEN //*Ballygawley*//
    SET WorkNum='028 855'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '016487' THEN //*Cookstown*//
    SET WorkNum='028 867'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '013655' THEN //*Fivemiletown*//
    SET WorkNum='028 895'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '018206' THEN //*Banbridge*//
    SET WorkNum='028 406'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '016937' THEN //*Rostrevor*//
    SET WorkNum='028 417'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '012477' THEN //*Kircubbin*//
    SET WorkNum='028 427'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '013967' THEN //*Newcastle*//
    SET WorkNum='028 437'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '013657' THEN //*Lisnaskea*//
    SET WorkNum='028 677'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum) WHEN '013656' THEN //*Kesh*//
    SET WorkNum='028 686'+substr(WorkNum,6,1)+' '+substr(WorkNum,7,46);
    RETURN(WorkNum)
  END CASE
  ;
  //// CHECK FOR OLD STYLE mobile numbers
  IF "left"(WorkNum,4) = '0374' AND "left"(WorkNum,5) <> '03745' THEN
    SET WorkNum='07'+substr(WorkNum,2,3)+' '+substr(WorkNum,5,46);
    RETURN(WorkNum)
  END IF;
  IF "left"(WorkNum,2) = '04' THEN
    SET WorkNum='07'+substr(WorkNum,2,3)+' '+substr(WorkNum,5,46);
    RETURN(WorkNum)
  END IF;
  IF "left"(WorkNum,4) IN('0585','0589','0705','0802','0831','0850','0860','0881','0973','0976','0966') THEN
    SET WorkNum='07'+substr(WorkNum,2,3)+' '+substr(WorkNum,5,46);
    RETURN(WorkNum)
  END IF;
  IF "left"(WorkNum,5) IN('08362','08363','08365','08366','08367') THEN
    SET WorkNum='07'+substr(WorkNum,2,3)+' '+substr(WorkNum,5,46);
    RETURN(WorkNum)
  END IF;
  RETURN(NUMBER)
END
}