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
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('little@stack.com, foot@yahoo') mail_2
3 from dual;
MAIL_1 MAIL_2
---------------------------------------- ----------------------------------------
a@b: invalid e-mail address foot@yahoo: invalid e-mail address
SQL>
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;
}
}
