====== 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>
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
</code>