====== pears.PhoneUpdate ======
<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"."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
}
</code>