Show pageOld revisionsBacklinksExport to PDFFold/unfold allBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ====== 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> database/functions/pears_phoneupdate.txt Last modified: 2026/08/07 19:24by 127.0.0.1
127.0.0.1