Filter out tokens that’s not an email or phone number-CodePudding

I’m checking to see if a token is not a phone number or email because it has to be one of those.

I know how to do email:

Token NOT LIKE ‘%@%’

Not sure how remove phone numbers that have 10 characters length.

All is being coded in Oracle SQL.

I tried:

TOKEN NOT LIKE ‘%@%’
AND LENGTH(TOKEN) > 10

I got one result back but is this properly check for others that aren’t a phone number token

CodePudding user response：

Well, checks you are performing are rather week. a@b contains the @ sign, but that's not a valid e-mail address. Or, string whose length is lower than 10 characters may be 123AB-$)1 but that's not a valid phone number.

I'm not saying that functions I'm going to post do it 100% right, but should be a lot better than what you're trying to do. Adjust them, if you want.

Phone numbers:

CREATE OR REPLACE
   FUNCTION f_phone (par_telefon IN VARCHAR2)
      RETURN VARCHAR2
   IS
      /* 21.11.2022 Check whether phone number(s) passed via PAR_TELEFON are
                    valid.
                    
                    Check:
                    - length has to be between 6 (e.g. 654123) and 20 (e.g.  385 91/123-4567) characters
                    - can contain signs:  -/, space, digits
                    
                    Return:
                    - phone number and its errors (if there are any)
                    - NULL if everything is OK
      */
      retval  VARCHAR2 (200);
      l_str   VARCHAR2 (200);     -- list of errors
      l_cnt   NUMBER := 0;        -- number of errors
   BEGIN
      FOR cur_r IN (    SELECT REGEXP_SUBSTR (par_telefon,
                                              '[^,] ',
                                              1,
                                              LEVEL) val
                          FROM DUAL
                    CONNECT BY LEVEL <= REGEXP_COUNT (par_telefon, ',')   1)
      LOOP
         IF LENGTH (cur_r.val) < 6
         THEN
            l_str := l_str || '; ' || 'too short';
            l_cnt := l_cnt   1;
         ELSIF LENGTH (cur_r.val) > 20
         THEN
            l_str := l_str || '; ' || 'too long';
            l_cnt := l_cnt   1;
         END IF;

         IF TRANSLATE (cur_r.val, '~1234567890-/  ', '~') IS NOT NULL
         THEN
            l_str := l_str || '; ' || 'allowed: 0-9  -/';
            l_cnt := l_cnt   1;
         END IF;

         IF l_cnt > 0
         THEN
            retval :=
               retval || '; ' || cur_r.val || ': ' || LTRIM (l_str, '; ');
            l_cnt := 0;
            l_str := NULL;
         END IF;
      END LOOP;

      RETURN ltrim(retval, '; ');
   END f_phone;
   /

E-mail addresses:

CREATE OR REPLACE
   FUNCTION f_e_mail (par_e_mail IN VARCHAR2)
      RETURN VARCHAR2
   IS
      /* 21.11.2022 Check e-mail addresses.
      */
      l_email  VARCHAR2 (200);
      retval   VARCHAR2 (200);
      l_str    VARCHAR2 (200);        -- list of errors
      l_cnt    NUMBER := 0;           -- number of errors
   BEGIN
      l_email := RTRIM (par_e_mail, CHR (13));

      FOR cur_r IN (    SELECT TRIM (REGEXP_SUBSTR (REPLACE (l_email, ' ', ''),
                                                    '[^,] ',
                                                    1,
                                                    LEVEL)) val
                          FROM DUAL
                    CONNECT BY LEVEL <= REGEXP_COUNT (l_email, ',')   1)
      LOOP
         IF NOT REGEXP_LIKE (
                   cur_r.val,
                   '^[a-zA-Z0-9._%-] @[a-zA-Z0-9._%-] \.[a-zA-Z]{2,4}$')
         THEN
            l_str := l_str || '; ' || 'invalid e-mail address';
            l_cnt := l_cnt   1;
         END IF;

         IF l_cnt > 0
         THEN
            retval :=
               retval || '; ' || cur_r.val || ': ' || LTRIM (l_str, '; ');
            l_cnt := 0;
            l_str := NULL;
         END IF;
      END LOOP;

      RETURN ltrim(retval, '; '); 
   END f_e_mail;
   /

A test or two:

SQL> select f_phone('555 123-456') phone_1,
  2         f_phone('00385-AB-234 234') phone_2,
  3         f_phone('123') phone_3,
  4         f_phone('456ab,  385 81 123-456') phone_4
  5  from dual;

PHONE_1    PHONE_2                                  PHONE_3         PHONE_4
---------- ---------------------------------------- --------------- ----------------------------------------
           00385-AB-234 234: allowed: 0-9  -/       123: too short  456ab: too short; allowed: 0-9  -/

SQL>
SQL> select f_e_mail('a@b') mail_1,
  2         f_e_mail('[email protected], foot@yahoo') mail_2
  3  from dual;

MAIL_1                                   MAIL_2
---------------------------------------- ----------------------------------------
a@b: invalid e-mail address              foot@yahoo: invalid e-mail address

SQL>

CodePudding user response：

The best way to validate an e-mail address is to send it an e-mail and get the owner to confirm that: it is a valid address; it is the correct address for the owner; and that they wanted to be signed up for your service.

Otherwise, if you want to test for a valid e-mail address and you have Java enabled in the database then you can use the JavaMail package.

Download the JavaMail API and then load the jar into the database with the loadjava utility.

Then compile the class in the database:

CREATE AND COMPILE JAVA SOURCE NAMED test_email AS
import javax.mail.internet.InternetAddress;
import javax.mail.internet.AddressException;

public class email {
  public static int validateEmail(String email) {
    try {
      InternetAddress emailAddr = new InternetAddress(email);
      emailAddr.validate();
    } catch (AddressException ex) {
      return 0;
    }
    return 1;
  }
}

Then you can create a PL/SQL wrapper function:

CREATE FUNCTION validateEmail RETURN NUMBER
AS LANGUAGE JAVA NAME 'email.validateEmail(java.lang.String) return int';
/

If you prefer, instead, you can load and use the Apache Commons Validator package:

CREATE AND COMPILE JAVA SOURCE NAMED test_email AS
import org.apache.commons.validator.routines.EmailValidator;

public class email {
  public static int validateEmail(String email) {
    return EmailValidator.getInstance().isValid(email) ? 1 : 0;
  }
}