This discussion is archived
14 Replies Latest reply: Mar 7, 2011 4:40 AM by 784804 RSS

Phone number validation in varchar2 field

784804 Newbie
Currently Being Moderated
Hello friends,

I have one database column in varchar2 , to store phone number in the format 23444-34545. [ code-phone no]
SO i want to check whether the user enter valid phone numbers in two different text fields in forms6i..ofcourse code&numbers..

My database version is : oracle 9i -9.2.0.8.0

How can i do this validation.. In oracle 9i REG-EXP is not available.So what can i do for this ?
  • 1. Re: Phone number validation in varchar2 field
    781692 Explorer
    Currently Being Moderated
    hi,
    Ae you planning to show two different fields in the canvas.
    if yes then your job will become easier.
    other wise by considering each character in the field(entered by the user) you have to check whether the entered is number or '-'. and also you have to take care that '-' is is correct position.
    first decide your layout.
    what i suggest is create two non-db columns :code and :ph_no
    of data type number. in pre-insert trigger
    :phone_number := :code||'-'||:ph_no
    since :code and :ph_no are number columns, so user cannot enter other than numbers. restrict the maximum length.

    so in query mode show the code and numbers through post-query trigger.
  • 2. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    Hi

    did u try to use the format mask ?!


    Pls try the following...
    99999-99999
    Regards,

    Abdetu...
  • 3. Re: Phone number validation in varchar2 field
    784804 Newbie
    Currently Being Moderated
    Ok after set this format mask,If the user enter character then,The error will throw. So how to give message to user
    if he enter wrongly.
  • 4. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    Hi

    Pls try useing TRANSLATE is helpful to do such a check... :)

    Another Idea...

    u can use a function like strip_bad You can also just use a double TRANSLATE to clean up the data and this u can Validate...

    CREATE OR REPLACE FUNCTION strip_bad (p_string IN VARCHAR2) 
    RETURN VARCHAR2 
    IS 
    good_string VARCHAR2(63) := '1234567890 ' || '-' ||  ; 
    
    BEGIN 
    
    RETURN 
    
    TRANSLATE (p_string, 'T' || 
    TRANSLATE ( p_string, '.' || good_string 
    , '.' 
    ) -- find the bad characters 
    , 'T'); -- remove the bad characters 
    END strip_bad; 
    / 
    
    -- For Example :
    
    select strip_bad('St. Loui$$s') NOW_BAD from dual 
    / 
    
    NOW_BAD 
    Hope this helps...

    or u catch the error in ON-ERROR Trigger

    Regards,

    Abdetu...

    Edited by: Abdetu on Mar 3, 2011 5:50 AM
  • 5. Re: Phone number validation in varchar2 field
    784804 Newbie
    Currently Being Moderated
    could u tell me how to translate
  • 6. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    Hi

    Pls try the following...
    SELECT CASE WHEN REGEXP_LIKE('23444-34545','^[0-9]{5}-[0-9]{5}$') 
    
    THEN 'Match Found' else 'No Match Found' end as output from dual;
    AFAIK REGEXP_LIKE is here for Oracle9i Application Server PL/SQL Web Toolkit Reference
    Release 1.0.2.2
    http://download.oracle.com/docs/cd/A97329_03/web.902/a90101/pspatt.htm#1005584

    but i guess urs is different no ?

    Regards,

    Abdetu...

    Edited by: Abdetu on Mar 3, 2011 4:53 AM
  • 7. Re: Phone number validation in varchar2 field
    792754 Pro
    Currently Being Moderated
    if data entry doesn't match the format mask, forms should give the message automatically, so there is no need to put extra messages ...
    if you want to do your own validation, remove the format mask and do something like this in the WHEN-VALIDATE-ITEM-trigger:
    DECLARE
        v_num NUMBER;
    BEGIN
        v_num := TO_NUMBER(:YOUR_BLOCK.YOUR_ITEM); -- assuming it's a char field
    EXCEPTION
        WHEN OTHERS THEN
            MESSAGE('Please enter in Format 99999');
            RAISE FORM_TRIGGER_FAILURE;
    END;
  • 8. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    u can also use the OWA_PATTERN it is supported for 9i

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/w_patt.htm#i999925


    but i don't have examples for them really.. :( but u can try the Edited POst TRANSLATE i Edited it above ... :)
    :)
    Regards,

    Abdetu...

    Edited by: Abdetu on Mar 3, 2011 5:23 AM
  • 9. Re: Phone number validation in varchar2 field
    784804 Newbie
    Currently Being Moderated
    Hello friends,

    I using two text items for CODE and NUMBER..this are number data types..Ok i met one problem now.
    If the user try to insert zero in the code in starting position then it does not allowed by CODE. it automatically
    truncate the zero and insert value in database. Example 02332-232434 ...What can i do
  • 10. Re: Phone number validation in varchar2 field
    CraigB Guru
    Currently Being Moderated
    If the user try to insert zero in the code in starting position then it does not allowed by CODE. it automatically
    truncate the zero and insert value in database. Example 02332-232434 ...What can i do >
    This is default functionality of a Column of Type NUMBER. Any leading zero is automatically dropped. In order to save a "Numeric" value with a leading Zero you will have to convert the Data Type of the Column in the tabl.e fro m NUMBER to VARCHAR2.

    Craig B-)

    If someone's response is helpful or correct, please mark it accordingly.

    Edited by: CraigB on Mar 4, 2011 10:09 AM
  • 11. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    Hi

    i agree with u Craig ...

    That's what i was trying to prove here ...

    Number field problem in forms. It does not allow zero

    Regards,

    Abdetu...
  • 12. Re: Phone number validation in varchar2 field
    784804 Newbie
    Currently Being Moderated
    Hai every one,

    Finally i done this.. I write a function to validate phone number in varchar2 field

    my function is :

    FUNCTION PHONE_VALIDATION(PH_IN in varchar) RETURN NUMBER IS

    chr char;
    asci number;
    no number;
    v_str varchar2(20):=PH_IN;
    BEGIN
         
    for i in 1..length(v_str)
    loop
    chr:=substr(v_str,i,1);
    asci:=ascii(chr);
    if asci not between 48 and 57 then
    no:=0;
    end if;
    exit when no=0;
    end loop;
    RETURN NO;

    END;




    I called this function from when validate trigger of the phone and code fields as follows


    DECLARE
    RESULT NUMBER;
    BEGIN
         RESULT:=PHONE_VALIDATION(:PH_NO);
              IF(RESULT=0)THEN
         MESSAGE('Please Enter valid phone number');
    MESSAGE('Please Enter valid phone number');
         --:PH_NO:=NULL;
         RAISE FORM_TRIGGER_FAILURE;
         -- :CONTACT_PHONE_NO:=:ph_code||'-'||:ph_no;
              END IF;
    END;



    finally i put this code and phone number in database column as follows :

    :CONTACT_PHONE_NO:=:PH_CODE||'-'||:PH_NO;
  • 13. Re: Phone number validation in varchar2 field
    784804 Newbie
    Currently Being Moderated
    Hai every one i done this...
  • 14. Re: Phone number validation in varchar2 field
    Amatu Allah Guru
    Currently Being Moderated
    Good to know :)

    PLs mark ur Question Answered Instead of Not

    Best Regards,

    Abdetu...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points