UK phone number change SQL

{ Informix SQL approach to the April 2000 UK phone number changes Doug Lawry, 22/04/2000 Email: doug_lawry@compuserve.com Phone: +44 780 121 9710 Notes: Spaces and punctuation in phone numbers are ignored. Values that do not match or contain alpha data are unchanged. Old numbers still work with the full STD code till September. Local number dialling must be altered with immediate effect. See www.numberchange.org for more information. } CREATE TABLE uk_number_change ( old_prefix CHAR(11) NOT NULL, new_prefix CHAR(11) NOT NULL, old_match CHAR(11) NOT NULL, new_match CHAR(11) NOT NULL ); LOAD FROM "uk_number_change.unl" INSERT INTO uk_number_change; CREATE PROCEDURE uk_number_change (p_number CHAR(40)) RETURNING CHAR(40); DEFINE l_old CHAR(11); DEFINE l_new CHAR(11); DEFINE l_temp CHAR(40); DEFINE l_number CHAR(40); DEFINE l_count SMALLINT; LET l_old = NULL; LET l_new = NULL; LET l_temp = p_number; LET l_number = " "; WHILE LENGTH(l_temp) > 0 IF l_temp[1] MATCHES "[0-9A-Za-z]" THEN LET l_number = TRIM(l_number) || l_temp[1]; END IF LET l_temp = l_temp[2,40]; END WHILE SELECT old_prefix, new_prefix INTO l_old, l_new FROM uk_number_change WHERE l_number MATCHES old_match; IF LENGTH(l_old) = 4 THEN RETURN TRIM(l_new) || l_number[5,11]; ELIF LENGTH(l_old) = 5 THEN RETURN TRIM(l_new) || l_number[6,11]; ELIF LENGTH(l_old) = 6 THEN RETURN TRIM(l_new) || l_number[7,11]; ELSE RETURN p_number; END IF END PROCEDURE; GRANT EXECUTE ON uk_number_change TO PUBLIC; { Example applications: SELECT number, uk_number_change(number) FROM phone_numbers; UPDATE phone_numbers SET number = uk_number_change(number); Contents of "uk_number_change.unl" (see www.numberchange.org): 01203|02476|01203??????|02476?????? 01222|02920|01222??????|02920?????? 01232|02890|01232??????|02890?????? 01238|02897|01238??????|02897?????? 012472|028912|012472?????|028912????? 012474|028914|012474?????|028914????? 012475|028915|012475?????|028915????? 012477|028427|012477?????|028427????? 012478|028918|012478?????|028918????? 012652|028702|012652?????|028702????? 012653|028703|012653?????|028703????? 012654|0287034|012654????|0287034???? 012655|0287035|012655????|0287035???? 012656|028276|012656?????|028276????? 012657|028207|012657?????|028207????? 012658|028708|012658?????|028708????? 012663|028253|012663?????|028253????? 012664|0282564|012664????|0282564???? 012665|028295|012665?????|028295????? 012666|028256|012666?????|028256????? 012667|028217|012667?????|028217????? 012668|028258|012668?????|028258????? 013653|028663|013653?????|028663????? 013654|028664|013654?????|028664????? 013655|028895|013655?????|028895????? 013656|028686|013656?????|028686????? 013657|028677|013657?????|028677????? 013965|028445|013965?????|028445????? 013966|028446|013966?????|028446????? 013967|028437|013967?????|028437????? 013968|028448|013968?????|028448????? 013991|076991|013991?????|076991????? 013992|076992|013992?????|076992????? 013993|076993|013993?????|076993????? 013994|076994|013994?????|076994????? 013995|076995|013995?????|076995????? 013996|076996|013996?????|076996????? 013997|076997|013997?????|076997????? 013998|076998|013998?????|076998????? 013999|076999|013999?????|076999????? 01426|07626|01426??????|07626?????? 01459|07659|01459??????|07659?????? 01481|014812|01481?????|014812????? 015042|028712|015042?????|028712????? 015043|028713|015043?????|028713????? 015046|028716|015046?????|028716????? 015047|028777|015047?????|028777????? 015048|028718|015048?????|028718????? 01523|07623|01523??????|07623?????? 01574|02828|01574??????|02828?????? 016482|028792|016482?????|028792????? 016483|028793|016483?????|028793????? 016484|028794|016484?????|028794????? 016485|028795|016485?????|028795????? 016487|028867|016487?????|028867????? 01648|028796|01648?????|028796????? 016622|028822|016622?????|028822????? 016624|028824|016624?????|028824????? 016625|028855|016625?????|028855????? 016626|028816|016626?????|028816????? 016627|028807|016627?????|028807????? 016628|028828|016628?????|028828????? 016932|028302|016932?????|028302????? 016933|028303|016933?????|028303????? 016936|0283026|016936????|0283026???? 016937|028417|016937?????|028417????? 016938|028308|016938?????|028308????? 01703|02380|01703??????|02380?????? 01705|02392|01705??????|02392?????? 0171|0207|0171???????|0207??????? 01762|02838|01762??????|02838?????? 0181|0208|0181???????|0208??????? 018206|028406|018206?????|028406????? 01846|02892|01846??????|02892?????? 01849|02894|01849??????|02894?????? 01861|02837|01861??????|02837?????? 01868|02887|01868??????|02887?????? 01893|07693|01893??????|07693?????? 01960|02893|01960??????|02893?????? 0321|08080|0321??????|08080?????? 0345|08457|0345??????|08457?????? 0370|07770|0370??????|07770?????? 03740|077740|03740?????|077740????? 03741|077741|03741?????|077741????? 03742|077742|03742?????|077742????? 03743|077743|03743?????|077743????? 03744|077744|03744?????|077744????? 037450|0870450|037450????|0870450???? 037451|0870451|037451????|0870451???? 037452|0870452|037452????|0870452???? 037453|0870453|037453????|0870453???? 037454|0870454|037454????|0870454???? 037455|0870455|037455????|0870455???? 037456|0870456|037456????|0870456???? 037457|0870457|037457????|0870457???? 037459|0870459|037459????|0870459???? 03746|077746|03746?????|077746????? 03747|077747|03747?????|077747????? 03748|077748|03748?????|077748????? 03749|077749|03749?????|077749????? 0378|07778|0378??????|07778?????? 0385|07785|0385??????|07785?????? 0401|07701|0401??????|07701?????? 0402|07702|0402??????|07702?????? 0403|07703|0403??????|07703?????? 0410|07710|0410??????|07710?????? 0411|07711|0411??????|07711?????? 0421|07721|0421??????|07721?????? 04325|07625|04325??????|07625?????? 0441|07741|0441??????|07741?????? 04481|07781|04481??????|07781?????? 04624|07624|04624??????|07624?????? 0467|07767|0467??????|07767?????? 0468|07768|0468??????|07768?????? 0498|07798|0498??????|07798?????? 0541|08701|0541??????|08701?????? 0585|07885|0585??????|07885?????? 0589|07889|0589??????|07889?????? 0645|08459|0645??????|08459?????? 0802|07802|0802??????|07802?????? 0831|07831|0831??????|07831?????? 0836|07836|0836??????|07836?????? 0850|07850|0850??????|07850?????? 0860|07860|0860??????|07860?????? 09307|079307|09307?????|079307????? 09411|076411|09411?????|076411????? 09561|079561|09561?????|079561????? 09562|079562|09562?????|079562????? 09563|079563|09563?????|079563????? 09564|079564|09564?????|079564????? 09565|079565|09565?????|079565????? 09566|079566|09566?????|079566????? 09567|070107|09567?????|070107????? 09568|079568|09568?????|079568????? 09569|079569|09569??