June 29, 2018 at 12:51 pm
Greetings,
I'm working on a project where we have to take a list of unique phone numbers in our database and scrub them against the National DNC list - that part isn't in question here.
For legal reasons, we have to use a third-party vendor that maintains the DNC list - we upload our list, they scrub, and then send back. However, they charge per-number to scrub, which means that it is in our best interests to remove invalid phone numbers (e.g. 9999999999) from the list before sending.
So while I can write obvious rules for numbers like the above, there are thousands more variations that aren't valid - either due to the customer mis-inputting, or the Agent doing so or whatever - and my manual list would quickly grow out of control.
Does someone have, or is aware of, a function, etc. that does basic checks against a 10-digit phone number to help identify ones that are invalid? I realize that some are still going to slip through, but even if I were to identify 10%, that would reduce our costs significantly. I'm not looking to make sure that the number is active, etc. - rather that it's even valid - so that something like 1119992222 is removed.
And yes - we keep track of the numbers we've already scrubbed so we're not re-sending them. Our weekly volume is 500K+ new numbers.
June 29, 2018 at 1:21 pm
You could try to use the functions presented in the following article:
http://www.sqlservercentral.com/scripts/String+Function/141686/
June 30, 2018 at 6:25 am
There could not be a universal function for this purpose.
Rules are different from country to country, from area to area, from provider to provider.
You need to know what are the criteria for acceptable numbers and build validation rules around it.
_____________
Code for TallyGenerator
July 1, 2018 at 4:36 pm
Sergiy - Saturday, June 30, 2018 6:25 AMThere could not be a universal function for this purpose.Rules are different from country to country, from area to area, from provider to provider.You need to know what are the criteria for acceptable numbers and build validation rules around it.
From what I understood, the OP just needs to remove all formatting to compare it against a Do Not Call list. There's no request to validate the number to be real.
July 1, 2018 at 8:25 pm
Luis Cazares - Sunday, July 1, 2018 4:36 PMSergiy - Saturday, June 30, 2018 6:25 AMThere could not be a universal function for this purpose.Rules are different from country to country, from area to area, from provider to provider.You need to know what are the criteria for acceptable numbers and build validation rules around it.From what I understood, the OP just needs to remove all formatting to compare it against a Do Not Call list. There's no request to validate the number to be real.
If you look again at the top post you'll find quite a different request:
N.B. - Friday, June 29, 2018 12:51 PM,,, it is in our best interests to remove invalid phone numbers (e.g. 9999999999) from the list before sending.
...
I'm not looking to make sure that the number is active, etc. - rather that it's even valid - so that something like 1119992222 is removed.
...
The examples above do not have any formatting in the strings. Strictly digits.
And still - they have to be ruled out by the function before the actual validation.
And I can say - the number 9999999999 might be perfectly valid in the town where I live.
It's quite possible we have even local numbers starting with "911".
It's because our emergency number is "111".
_____________
Code for TallyGenerator
July 2, 2018 at 3:43 am
Sergiy - Sunday, July 1, 2018 8:25 PMLuis Cazares - Sunday, July 1, 2018 4:36 PMSergiy - Saturday, June 30, 2018 6:25 AMThere could not be a universal function for this purpose.Rules are different from country to country, from area to area, from provider to provider.You need to know what are the criteria for acceptable numbers and build validation rules around it.From what I understood, the OP just needs to remove all formatting to compare it against a Do Not Call list. There's no request to validate the number to be real.
If you look again at the top post you'll find quite a different request:
N.B. - Friday, June 29, 2018 12:51 PM,,, it is in our best interests to remove invalid phone numbers (e.g. 9999999999) from the list before sending.
...
I'm not looking to make sure that the number is active, etc. - rather that it's even valid - so that something like 1119992222 is removed.
...The examples above do not have any formatting in the strings. Strictly digits.
And still - they have to be ruled out by the function before the actual validation.And I can say - the number 9999999999 might be perfectly valid in the town where I live.
It's quite possible we have even local numbers starting with "911".
It's because our emergency number is "111".
Not if your network provider is following international conventions it can't.
Numbers beginning 999, 111 and 911 are reserved for emergency services 112 may also be reserved as a lower priority number to the emergency services
In most locales any number starting with a number other than 0 is a local number. All national numbers are 11 digits in length ( except for Republic of Ireland which adds a 12th digit for voicemail numbers to the users normal number)
International numbers are 13 digits. You drop the initial 0 from the national number and prepend the international dialing code which also begins with a 0 but by convention that 0 is often written as +, countries which do not use 0 to identify non local numbers will have two digit international codes
You should be able to get a list of area codes for your country easily enough, similarly international dialing codes are readily available.
To further complicate matters
Some companies will write their number using the letters on the keypad - convert these to the numeric equivalents - seems common practice in the US - I haven't seen it elsewhere.
You may see numbers preceded with a PBAX access code - usually 9 or 0
You may have an extension number postpended (usually preceded by an x)
July 2, 2018 at 6:00 am
There is no such thing as international conventions in telephone numbers.
In Australia emergency number is "000", and long distace calls start with "1".
In neighbouring New Zealand it's exactly opposite.
And both have nothing in common with ex-USSR countries, which use "01" for fire emergency, "02" for police and "03" for ambulance.
In France international calls start with 8-10, same as ex-USSR. Must be similar in in some other countries, where French developed telephone network.
International code for USA is 1 digit long, for UK it's 2 digits long, for Ukraine its 3 digits long.
In New Zealand all land line numbers are 2+7 digits long (area code+local number), and all mobile numbers are 3+(7 or 6) digits.
Quite different from Australia where local numbers are 8 digits long almost everywhere.
Which of those "international conventions" is still standing?
There is only one rule - you cannot rely on any universal rule when it comes to phone numbers.
_____________
Code for TallyGenerator
July 2, 2018 at 6:56 am
The whole purpose of the third party software is to do exactly what is being requested and it's being done for "legal reasons". Pre-filtering is not only more difficult than you think, but may violate the "legal reasons" for hiring the third party to do so. Because you're not an "expert" at this and don't actually have access to all that you need to filter yourself, you may end up pre-filtering out a number that is actually a valid number and then you'd be in deep Kimchi legally.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2018 at 9:06 am
N.B. - Friday, June 29, 2018 12:51 PMGreetings,I'm working on a project where we have to take a list of unique phone numbers in our database and scrub them against the National DNC list - that part isn't in question here.
For legal reasons, we have to use a third-party vendor that maintains the DNC list - we upload our list, they scrub, and then send back. However, they charge per-number to scrub, which means that it is in our best interests to remove invalid phone numbers (e.g. 9999999999) from the list before sending.
So while I can write obvious rules for numbers like the above, there are thousands more variations that aren't valid - either due to the customer mis-inputting, or the Agent doing so or whatever - and my manual list would quickly grow out of control.
Does someone have, or is aware of, a function, etc. that does basic checks against a 10-digit phone number to help identify ones that are invalid? I realize that some are still going to slip through, but even if I were to identify 10%, that would reduce our costs significantly. I'm not looking to make sure that the number is active, etc. - rather that it's even valid - so that something like 1119992222 is removed.
And yes - we keep track of the numbers we've already scrubbed so we're not re-sending them. Our weekly volume is 500K+ new numbers.
If your numbers are ALL numbers that are supposed to exist in the US only, you can filter out invalid numbers, but... and it could be a legal trouble kind of but, how do you intend to keep your filter up to date? Do you have contacts at EVERY single RBOC in the country? Without an "in" at every one of those, you're not likely to be able to keep up with knowing all the valid area codes and the valid prefixes within each area code, in a way that has much of a shot at keeping you sufficiently up to date. Want to bet your job on that? Also, just from a purely practical perspective, let's stop and think about how quickly you'd run across every single number in the country at a rate of 500K + per week. Even if you assume the current population growth into population projections, and figure that every person in the country has cell phone AND a land line, you'd only be looking at a total universe of 750 million numbers, and at 500K+ per week, you'd run out of new numbers in just 30 years.
Drop that initial estimate down to a lot closer to reality by just changing your assumption and then be generous and say you have 400 million phone numbers in use, then you run out of new numbers in just 16 years. My point being that at such a rate, a rather large number of the numbers you see every week would HAVE to have been numbers you've seen before. The difficulty is thus determining what timeframe to use to say that you already know what to do with that number, simply because you've seen it before. As you pay a service to validate against the DNC list, you would need to know how often that list is updated from the point of view of your vendor. Then you might be able to go quickly enough. to keep up to some degree, assuming your vendor is a little slower at staying up to date. Which means if you can't be any faster than your vendor can in terms of you staying up to date on what numbers you've seen before, than your vendor can stay up to date with the DNC list, then you would be wasting your time.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2018 at 11:08 am
Sergiy - Monday, July 2, 2018 6:00 AMThere is no such thing as international conventions in telephone numbers.In Australia emergency number is "000", and long distace calls start with "1".In neighbouring New Zealand it's exactly opposite.And both have nothing in common with ex-USSR countries, which use "01" for fire emergency, "02" for police and "03" for ambulance.In France international calls start with 8-10, same as ex-USSR. Must be similar in in some other countries, where French developed telephone network.International code for USA is 1 digit long, for UK it's 2 digits long, for Ukraine its 3 digits long.In New Zealand all land line numbers are 2+7 digits long (area code+local number), and all mobile numbers are 3+(7 or 6) digits.Quite different from Australia where local numbers are 8 digits long almost everywhere. Which of those "international conventions" is still standing?There is only one rule - you cannot rely on any universal rule when it comes to phone numbers.
The relevant standards are issued by ITU-T - The international Telecommunications Union.
E.164 governs the format of international numbers - all national numbers will be the part of an international number after the country code.
Yes the emergency number does vary between countries but because equipment suppliers want to sell to multiple countries they have in most cases taken to handling all those numbers together - the result of this is if you dial 999 in the US or 911 or 111 in the UK you still get put through to the emergency operator even though the number you have dialled is not the emergency number for that country.
The international dialling code for the UK is - 044, similarly for the US its 01 the first digit of that is an access code
For the US the dialling plan is made up of 3 parts Area Code, Region code and Subscriber number - a total of 10 digits
For the UK its two parts Area code and Subscriber number a total of 11 digits, the initial 0 is dropped when prepending 044 to give the international number.
Different countries are free to implement there own dialling plan but they must comply with ITU-T E.164 if they expect to be able to handle international calls. In practice they mostly use an area code followed by the subscriber number, the US because of its size has a third Region Code between those two parts and some very small countries are all a single area.
Within an area code the telephone operator may permit dialling using only the subscriber number and this is commonly permitted for landlines, mobile phones will usually require the full national number to be used.
Unless you are trading globally with a significant proportion of call being international calls I wouldn't see much value in validating those beyond the country code.
For numbers within your own country then validating the area code may be sensible as would ensuring the rest of the number matches your national dialing plan. Your telephone operator should be able to provide a list of area codes and also the dialling plan if you do not already know it.
I'd leave validation of the subscriber number to the validating company.
July 2, 2018 at 1:48 pm
Ok, so this blew up... In any event, the overall answer is that there really isn't a way to write rules that would help scrub out invalid phone numbers.
The application that intakes the data only checks to make sure that the input is all numeric - and then stores the result in a varchar(10) field. And this is United States only. We have zero control over the application; we only consume data from it for further customer activities.
Thanks for all the replies - this has certainly been... educational.
July 2, 2018 at 2:31 pm
crmitchell - Monday, July 2, 2018 11:08 AMThe relevant standards are issued by ITU-T - The international Telecommunications Union.
E.164 governs the format of international numbers - all national numbers will be the part of an international number after the country code.
Yes the emergency number does vary between countries but because equipment suppliers want to sell to multiple countries they have in most cases taken to handling all those numbers together - the result of this is if you dial 999 in the US or 911 or 111 in the UK you still get put through to the emergency operator even though the number you have dialled is not the emergency number for that country.The international dialling code for the UK is - 044, similarly for the US its 01 the first digit of that is an access code
For the US the dialling plan is made up of 3 parts Area Code, Region code and Subscriber number - a total of 10 digits
For the UK its two parts Area code and Subscriber number a total of 11 digits, the initial 0 is dropped when prepending 044 to give the international number.Different countries are free to implement there own dialling plan but they must comply with ITU-T E.164 if they expect to be able to handle international calls. In practice they mostly use an area code followed by the subscriber number, the US because of its size has a third Region Code between those two parts and some very small countries are all a single area.
Within an area code the telephone operator may permit dialling using only the subscriber number and this is commonly permitted for landlines, mobile phones will usually require the full national number to be used.Unless you are trading globally with a significant proportion of call being international calls I wouldn't see much value in validating those beyond the country code.
For numbers within your own country then validating the area code may be sensible as would ensuring the rest of the number matches your national dialing plan. Your telephone operator should be able to provide a list of area codes and also the dialling plan if you do not already know it.
I'd leave validation of the subscriber number to the validating company.
International code for UK is 44, for USA - 1, not 044 and 01.
Russia and France are pretty able to handle international calls. Despite their systems are quite different from the standard you refer to:
https://www.timeanddate.com/worldclock/dialingcodes.html?p1=166&p2=64&number=
So, that "relevant standard" is pretty irrelevant at least in some countries.
I just dialed "911" from my home phone - no emergency service popped up. it was waiting for me to continue dialing.
Same effect after dialing "999". Even though we're under the same Crown, UK emergency dialing code does not work in NZ or Australia.
Once again, all the rules and standards for phone calling used in your area are relevant only to your area.
And if I'd write a function to pre-validate phone numbers recorded in a database, it would have no value for the OP, as he's not in the same area as me.
_____________
Code for TallyGenerator
July 2, 2018 at 3:00 pm
Sergiy - Monday, July 2, 2018 2:31 PMcrmitchell - Monday, July 2, 2018 11:08 AMThe relevant standards are issued by ITU-T - The international Telecommunications Union.
E.164 governs the format of international numbers - all national numbers will be the part of an international number after the country code.
Yes the emergency number does vary between countries but because equipment suppliers want to sell to multiple countries they have in most cases taken to handling all those numbers together - the result of this is if you dial 999 in the US or 911 or 111 in the UK you still get put through to the emergency operator even though the number you have dialled is not the emergency number for that country.The international dialling code for the UK is - 044, similarly for the US its 01 the first digit of that is an access code
For the US the dialling plan is made up of 3 parts Area Code, Region code and Subscriber number - a total of 10 digits
For the UK its two parts Area code and Subscriber number a total of 11 digits, the initial 0 is dropped when prepending 044 to give the international number.Different countries are free to implement there own dialling plan but they must comply with ITU-T E.164 if they expect to be able to handle international calls. In practice they mostly use an area code followed by the subscriber number, the US because of its size has a third Region Code between those two parts and some very small countries are all a single area.
Within an area code the telephone operator may permit dialling using only the subscriber number and this is commonly permitted for landlines, mobile phones will usually require the full national number to be used.Unless you are trading globally with a significant proportion of call being international calls I wouldn't see much value in validating those beyond the country code.
For numbers within your own country then validating the area code may be sensible as would ensuring the rest of the number matches your national dialing plan. Your telephone operator should be able to provide a list of area codes and also the dialling plan if you do not already know it.
I'd leave validation of the subscriber number to the validating company.International code for UK is 44, for USA - 1, not 044 and 01.
Russia and France are pretty able to handle international calls. Despite their systems are quite different from the standard you refer to:https://www.timeanddate.com/worldclock/dialingcodes.html?p1=166&p2=64&number=
So, that "relevant standard" is pretty irrelevant at least in some countries.I just dialed "911" from my home phone - no emergency service popped up. it was waiting for me to continue dialing.
Same effect after dialing "999". Even though we're under the same Crown, UK emergency dialing code does not work in NZ or Australia.Once again, all the rules and standards for phone calling used in your area are relevant only to your area.
And if I'd write a function to pre-validate phone numbers recorded in a database, it would have no value for the OP, as he's not in the same area as me.
There's also the fact that "1" isn't actually for the "USA". It's for the "North American Numbering Plan" and includes Canada and the territories of the United States such as Guam and Puerto Rico, USVI, etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 3, 2018 at 2:55 am
Sergiy - Monday, July 2, 2018 2:31 PMcrmitchell - Monday, July 2, 2018 11:08 AMThe relevant standards are issued by ITU-T - The international Telecommunications Union.
E.164 governs the format of international numbers - all national numbers will be the part of an international number after the country code.
Yes the emergency number does vary between countries but because equipment suppliers want to sell to multiple countries they have in most cases taken to handling all those numbers together - the result of this is if you dial 999 in the US or 911 or 111 in the UK you still get put through to the emergency operator even though the number you have dialled is not the emergency number for that country.The international dialling code for the UK is - 044, similarly for the US its 01 the first digit of that is an access code
For the US the dialling plan is made up of 3 parts Area Code, Region code and Subscriber number - a total of 10 digits
For the UK its two parts Area code and Subscriber number a total of 11 digits, the initial 0 is dropped when prepending 044 to give the international number.Different countries are free to implement there own dialling plan but they must comply with ITU-T E.164 if they expect to be able to handle international calls. In practice they mostly use an area code followed by the subscriber number, the US because of its size has a third Region Code between those two parts and some very small countries are all a single area.
Within an area code the telephone operator may permit dialling using only the subscriber number and this is commonly permitted for landlines, mobile phones will usually require the full national number to be used.Unless you are trading globally with a significant proportion of call being international calls I wouldn't see much value in validating those beyond the country code.
For numbers within your own country then validating the area code may be sensible as would ensuring the rest of the number matches your national dialing plan. Your telephone operator should be able to provide a list of area codes and also the dialling plan if you do not already know it.
I'd leave validation of the subscriber number to the validating company.International code for UK is 44, for USA - 1, not 044 and 01.
Russia and France are pretty able to handle international calls. Despite their systems are quite different from the standard you refer to:https://www.timeanddate.com/worldclock/dialingcodes.html?p1=166&p2=64&number=
So, that "relevant standard" is pretty irrelevant at least in some countries.
Looks to follow the standard pretty well from where I'm sitting
You dial 81044 to call UK 0r 8101 for the US. I had already pointed out the 0 is to access code or in your case 810 - this is also why its usually written as +44 or +1
Then the area code - yep that follows for both
Then the subscriber number - looks the same to me
Max of 15 digits - yep that too.
Yes the access code varies by network operator or more generally country but you will be calling from likely a very few probably one so that's going to be known. The area code again will apply to that country - I had already stated it is unlikely you would want to validate those on international numbers so again it should be straightforward to validate against them.
I'm certainly not proposing a validation function covering every country - tailor it to your own requirements. Just that there are standards in place.
July 3, 2018 at 11:07 am
Once you've sent a 9-digit number to them, and they've validated it, you shouldn't need to send them that exact same number again. That is, keep a cache of the numbers already verified and don't send those again. You might want to put a time limit on the validation, so that after, say, nn months, you send them again, "just in case".
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply