SQL & PL/SQL

Hello 945437,

welcome to the forum.

Can you give us more details? Validating phone numbers is not a trivial task, see {thread:id=2414369}

Regards
Marcus

What kind of validation you are looking for?

If you search online you might be able to find something already written. Writing your own will ensure that it does precisely what you want.

Start by working out the logic of what you want to do and then implement it. If you don't know how to go about it start with pseudocode - a list of steps to do what you want to do.

Regarding the issue of procedure or function consider how you intend to use the program. If you want to use it in SQL then you must use a function and obey a bunch of rules (don't do certain things) for that to work. If you merely want to validate from other PL/SQL then a procedure might be best. Generally functions return values and procedures perform actions but there is a lot of overlap betwee the two. Also if you use a function you can always use a wrapper procedure to call it later.

Pseudocode to perform your validation could look something like (and there are lots of ways to do this)

set valid/invalid switch to valid
use SUBSTR() to get the country code
use SUBSTR() to get the area code (first 3 digits)
validate the country code - store valid ones in a table and perform a lookup. Set valid/invalid switch to invalid if not found, then exit
validate the area code - store valid ones in a table and perform a lookup. set valid/invalid switch to invalid if not found, then exit
validate the rest of the phone number. Make sure all values are digits. set valid/invalid switch to invalid if not found, then exit
exit with valid/invalid switch set to valid

You might be able to split the phone number if it is not stored as a single string, otherwise:

The country code has no fixed length http://en.wikipedia.org/wiki/List_of_country_calling_codes

The length of the area code might differ from country to country. Here in Germany it depends on the organization level. The capital of a federal state has 3 digits (e.g. Munich 089). Of course one less, if there exists a country code. The capital of a county has 4 digits and cities an other administrative districts within the county have 5 digits.

So you would need a complete ruleset for each country to validate a phone number.

Regards
Marcus

You are right. A lot of what the OP will have to do will depend on how his data looks. I was assuming fixed width data (being optimisitic that such would make things easier) but since the OP did not provide any data that and other assumptions will have to be adapted to reality.

Anyway I was just trying to give the OP an example of how to get started :)

But phone numbers are like a wild beast. Even with a lot of experience you can get hurt very badly, especially when someone treats them as numbers ;-) Therefore always caution beginners who want to tame them.

Hi ,
Thank you all for your replies.

I have to validate phone numbers of five countries ( India,Malaysia,Singapore,Australia,Hongkong) , I have to check their country codes + area code + length(local number) of all cellular networks available in all the above countries and have to update a flag column with yes or no .

How do I ask a question on the forums?
2176552

Do you know the possible combinations of "country codes + area code + length(local number)"?
If yes, then post the rules and we can help you. If not, then your first step should be to find out, e.g. in a forum specialized on telecommunication.

Regards
Marcus

Hi ,
You can perform this task using query... use substr and case

Hi ,
You can perform this task using query... use substr and case