pears.PhoneUpdate

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) //*Martinstown*//
  WHEN '012667' THEN SET "WorkNum" = '028 217'+"substr"("WorkNum",7,1)+' '+"substr"("WorkNum",8,46);
    RETURN("WorkNum") //*Limavady*//
  WHEN '015047' THEN SET "WorkNum" = '028 777'+"substr"("WorkNum",7,1)+' '+"substr"("WorkNum",8,46);
    RETURN("WorkNum") //*Carrickmore*//
  WHEN '016627' THEN 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) //*Cardiff*//
  WHEN '01222' THEN SET "WorkNum" = '029 20'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Coventry*//
  WHEN '01203' THEN SET "WorkNum" = '024 76'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Portsmouth*//
  WHEN '01705' THEN SET "WorkNum" = '023 92'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Southampton*//
  WHEN '01703' THEN SET "WorkNum" = '023 80'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Belfast*//
  WHEN '01232' THEN SET "WorkNum" = '028 90'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Ballymena - check 5 or 6 digit local number*//
  WHEN '01266' THEN 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") //*Larne*//
  WHEN '01754' THEN SET "WorkNum" = '028 28'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Londonderry*//
  WHEN '01504' THEN SET "WorkNum" = '028 71'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Colraine - check 5 or 6 digit local number*//
  WHEN '01265' THEN 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") //*Magherafelt - check 5 or 6 digit local number*//
  WHEN '01648' THEN 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") //*Bangor*//
  WHEN '01247' THEN SET "WorkNum" = '028 91'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Lisburn*//
  WHEN '01846' THEN SET "WorkNum" = '028 92'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Ballyclare*//
  WHEN '01960' THEN SET "WorkNum" = '028 93'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Antrim*//
  WHEN '01849' THEN SET "WorkNum" = '028 94'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Saintfield*//
  WHEN '01238' THEN SET "WorkNum" = '028 97'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Omagh*//
  WHEN '01662' THEN SET "WorkNum" = '028 82'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Dungannon*//
  WHEN '01868' THEN SET "WorkNum" = '028 87'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Downpatrick*//
  WHEN '01396' THEN SET "WorkNum" = '028 44'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Newry - check 5 or 6 digit local number*//
  WHEN '01693' THEN 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") //*Armagh*//
  WHEN '01861' THEN SET "WorkNum" = '028 37'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Portadown*//
  WHEN '01762' THEN SET "WorkNum" = '028 38'+"substr"("WorkNum",6,2)+' '+"substr"("WorkNum",8,45);
    RETURN("WorkNum") //*Enniskillen*//
  WHEN '01365' THEN 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) //*Ballycastle*//
  WHEN '012657' THEN SET "WorkNum" = '028 207'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Ballymoney*//
  WHEN '012656' THEN SET "WorkNum" = '028 276'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Kilrea*//
  WHEN '012665' THEN SET "WorkNum" = '028 295'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Newtonstewart*//
  WHEN '016626' THEN SET "WorkNum" = '028 816'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Ballygawley*//
  WHEN '016625' THEN SET "WorkNum" = '028 855'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Cookstown*//
  WHEN '016487' THEN SET "WorkNum" = '028 867'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Fivemiletown*//
  WHEN '013655' THEN SET "WorkNum" = '028 895'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Banbridge*//
  WHEN '018206' THEN SET "WorkNum" = '028 406'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Rostrevor*//
  WHEN '016937' THEN SET "WorkNum" = '028 417'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Kircubbin*//
  WHEN '012477' THEN SET "WorkNum" = '028 427'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Newcastle*//
  WHEN '013967' THEN SET "WorkNum" = '028 437'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Lisnaskea*//
  WHEN '013657' THEN SET "WorkNum" = '028 677'+"substr"("WorkNum",6,1)+' '+"substr"("WorkNum",7,46);
    RETURN("WorkNum") //*Kesh*//
  WHEN '013656' THEN 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