Validar datos de entrada con expresiones regulares en PLSQL de Oracle

View All Public Scripts Login to Run Script

Script Name Validar datos de entrada con expresiones regulares en PLSQL de Oracle
Visibility Unlisted - anyone with the share link can access
Description Función para validar datos de entradas con diferentes patrones de expresiones regulares.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor jjroropez@gmail.com
Created Saturday November 16, 2019

Statement 1

CREATE OR REPLACE FUNCTION F_VALIDATE_WITH_REGEXP   
      (p_str IN VARCHAR2,    
       p_type IN VARCHAR2 DEFAULT 'alphanumeric')   
RETURN BOOLEAN   
IS   
   
 v_regexp VARCHAR2(1000) := '^([\w\s]*?)+';   
 v_result NUMBER := 0;   
BEGIN   
   
 CASE p_type   
  WHEN 'alphanumeric' THEN v_regexp := '^([\w\s]*?)+';   
  WHEN 'date_dd/mm/yyy' THEN v_regexp := '^([0-9]{2})\/([0-9]{2})\/([0-9]{4})';   
  WHEN 'date_mm-dd-yyyy' THEN v_regexp := '^([0-9]{2})-([0-9]{2})-([0-9]{4})';   
  WHEN 'date_yyyy-mm-dd' THEN v_regexp := '^([0-9]{4})-([0-9]{2})-([0-9]{2})';   
  WHEN 'date_yyyy-mm' THEN v_regexp := '^([0-9]{4})-([0-9]{2})';   
  WHEN 'date_yyyy' THEN v_regexp := '^[0-9]{4}';   
  WHEN 'datetime_yyyy-mm-dd_hh:mm:ss' THEN v_regexp := '^([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})';   
  WHEN 'decimal' THEN v_regexp := '^[-]?[0-9]+(\.[0-9]{0,2})';   
  WHEN 'domain' THEN v_regexp := '(^(https:|http:)\/\/(www\.)?([a-zA-Z0-9\_\-\/]+\.)(com|net|org|biz|info|xyz|online|club|dev|live|space|app|site|shop|life|io|store|tech|news|media|design|guru|photography|global|rocks|today|movie|[a-z]{2,15})?(\.)?([a-z]{2,5}))';   
  WHEN 'email' THEN v_regexp := '^(\w+)(\w\-\.+)?@(\w+)\.([a-z]{2,6}|es|aero|asia|arpa|biz|cat|com|coop|edu|gov|info|int|jobs|mil|mobi|museum|name|movie|net|org|com\.ve|com\.ar|com\.co|com\.br|com\.cl|com\.mx|com\.pe|com\.uy|com\.py)';   
  WHEN 'http' THEN v_regexp := '^(https:|http:)';   
  WHEN 'images' THEN v_regexp := '([^\s]+(\.(jpe?g|png|gif|jpg)))';   
  WHEN 'ipaddr' THEN v_regexp := '^([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})';   
  WHEN 'numeric' THEN v_regexp := '^[-]?[0-9]+';    
  WHEN 'md5' THEN v_regexp := '[a-zA-Z0-9]{32}';   
  WHEN 'sha1' THEN v_regexp := '[a-zA-Z0-9]{40}';   
  WHEN 'sha256' THEN v_regexp := '[a-zA-Z0-9]{64}';   
  WHEN 'onechar' THEN v_regexp := '^([a-zA-Z]){1}';   
  WHEN 'phone' THEN v_regexp := '^\([0-9]{1,4}\)\s([0-9]{3}\s[0-9\-]{4})';   
  WHEN 'taxinfo' THEN v_regexp := '^[a-zA-Z0-9\-]{1,}[a-z0-9\s\.\-]';   
  WHEN 'url' THEN v_regexp := '(^(https:|http:)\/\/(www\.)?([a-zA-Z0-9\_\-]+\.)(com|net|org|biz|info|xyz|online|club|dev|live|space|app|site|shop|life|io|store|tech|news|media|design|guru|photography|global|rocks|today|movie|[a-z]{2,15})?(\.)?([a-z]{2,5})([a-zA-Z0-9\%\/\?\+\-\_\=\&\#]+))';   
  ELSE v_regexp := '^([\w\s]*?)+';   
 END CASE;   
   
 SELECT REGEXP_INSTR (p_str, v_regexp)    
 INTO v_result   
 FROM DUAL;   
    
 IF v_result <= 0 THEN   
  RETURN FALSE;   
 ELSE   
  RETURN TRUE;   
 END IF;   
      
EXCEPTION  
WHEN OTHERS   
 THEN   
  DBMS_OUTPUT.PUT_LINE ('ERROR:' || ' (' || sqlcode || ') '||sqlerrm);   
  RETURN FALSE;   
END F_VALIDATE_WITH_REGEXP;

Function created.

Statement 2

DECLARE   
 v_res_alphanumeric BOOLEAN := FALSE;    
 v_res_date1 BOOLEAN := FALSE;    
 v_res_date2 BOOLEAN := FALSE;    
 v_res_date3 BOOLEAN := FALSE;    
 v_res_date4 BOOLEAN := FALSE;    
 v_res_date5 BOOLEAN := FALSE;    
 v_res_datetime BOOLEAN := FALSE;    
 v_res_decimal BOOLEAN := FALSE;    
 v_res_domain BOOLEAN := FALSE;    
 v_res_email BOOLEAN := FALSE;    
 v_res_http BOOLEAN := FALSE;    
 v_res_image BOOLEAN := FALSE;    
 v_res_ipaddr BOOLEAN := FALSE;    
 v_res_numeric BOOLEAN := FALSE;    
 v_res_md5 BOOLEAN := FALSE;    
 v_res_sha1 BOOLEAN := FALSE;    
 v_res_sha256 BOOLEAN := FALSE;    
 v_res_onechar BOOLEAN := FALSE;    
 v_res_phone BOOLEAN := FALSE;    
 v_res_taxinfo BOOLEAN := FALSE;    
 v_res_url BOOLEAN := FALSE;    
BEGIN   
    
 v_res_alphanumeric := F_VALIDATE_WITH_REGEXP ('hola mundo', 'alphanumeric');   
 IF v_res_alphanumeric THEN   
  DBMS_OUTPUT.PUT_LINE('ALPHANUMERIC IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('ALPHANUMERIC IS FALSE');   
 END IF;   
    
 v_res_date1 := F_VALIDATE_WITH_REGEXP ('12/10/1964', 'date_dd/mm/yyyy');   
 IF v_res_date1 THEN   
  DBMS_OUTPUT.PUT_LINE('DATE1 (dd/mm/yyyy) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATE1 (dd/mm/yyyy) IS FALSE');   
 END IF;   
    
 v_res_date2 := F_VALIDATE_WITH_REGEXP ('07-20-1972','date_mm-dd-yyyy');   
 IF v_res_date2 THEN   
  DBMS_OUTPUT.PUT_LINE('DATE2 (mm-dd-yyyy) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATE2 (mm-dd-yyyy) IS FALSE');   
 END IF;    
    
 v_res_date3 := F_VALIDATE_WITH_REGEXP ('1980-08-18','date_yyyy-mm-dd');   
 IF v_res_date3 THEN   
  DBMS_OUTPUT.PUT_LINE('DATE3 (yyyy-mm-dd) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATE3 (yyyy-mm-dd) IS FALSE');   
 END IF;     
    
 v_res_date4 := F_VALIDATE_WITH_REGEXP ('1994-05','date_yyyy-mm');   
 IF v_res_date4 THEN   
  DBMS_OUTPUT.PUT_LINE('DATE4 (yyyy-mm) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATE4 (yyyy-mm) IS FALSE');   
 END IF;     
    
 v_res_date5 := F_VALIDATE_WITH_REGEXP ('1978', 'date_yyyy');   
 IF v_res_date5 THEN   
  DBMS_OUTPUT.PUT_LINE('DATE5 (yyyy) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATE5 (yyyy) IS FALSE');   
 END IF;     
    
 v_res_datetime := F_VALIDATE_WITH_REGEXP ('1901-01-22 16:00:30','datetime_yyyy-mm-dd_hh:mm:ss');   
 IF v_res_datetime THEN   
  DBMS_OUTPUT.PUT_LINE('DATETIME (yyyy-mm-dd hh:mm:ss) IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DATETIME (yyyy-mm-dd hh:mm:ss) IS FALSE');   
 END IF;     
    
 v_res_decimal := F_VALIDATE_WITH_REGEXP ('23981.98','decimal');   
 IF v_res_decimal THEN   
  DBMS_OUTPUT.PUT_LINE('DECIMAL IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DECIMAL IS FALSE');   
 END IF;   
    
 v_res_domain := F_VALIDATE_WITH_REGEXP ('https://enorable.com','domain');   
 IF v_res_domain THEN   
  DBMS_OUTPUT.PUT_LINE('DOMAIN IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('DOMAIN IS FALSE');   
 END IF;   
    
 v_res_email := F_VALIDATE_WITH_REGEXP ('jaooauq_aa909@gmail.com','email');   
 IF v_res_email THEN   
  DBMS_OUTPUT.PUT_LINE('EMAIL IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('EMAIL IS FALSE');   
 END IF;   
    
 v_res_http:= F_VALIDATE_WITH_REGEXP ('https://google.com','http');    
 IF v_res_http THEN   
  DBMS_OUTPUT.PUT_LINE('HTTP IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('HTTP IS FALSE');   
 END IF;   
    
 v_res_image := F_VALIDATE_WITH_REGEXP ('thisistheimage.jpeg','image');   
 IF v_res_image THEN   
  DBMS_OUTPUT.PUT_LINE('IMAGE IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('IMAGE IS FALSE');   
 END IF;   
    
 v_res_ipaddr := F_VALIDATE_WITH_REGEXP ('192.168.0.1','ipaddr');   
 IF v_res_ipaddr THEN   
  DBMS_OUTPUT.PUT_LINE('IPADDR IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('IPADDR IS FALSE');   
 END IF;   
    
 v_res_numeric := F_VALIDATE_WITH_REGEXP ('-12309','numeric');   
 IF v_res_numeric THEN   
  DBMS_OUTPUT.PUT_LINE('NUMERIC IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('NUMERIC IS FALSE');   
 END IF;   
    
 v_res_md5 := F_VALIDATE_WITH_REGEXP ('a98sj183ydn3j4bg8du2l930spao12j4','md5');    
 IF v_res_md5 THEN   
  DBMS_OUTPUT.PUT_LINE('MD5 IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('MD5 IS FALSE');   
 END IF;    
   
 v_res_sha1 := F_VALIDATE_WITH_REGEXP ('a98sj183ydn3j4bg8du2l930spao12j4nj28fhr6','sha1');   
 IF v_res_sha1 THEN   
  DBMS_OUTPUT.PUT_LINE('SHA1 IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('SHA1 IS FALSE');   
 END IF;    
    
 v_res_sha256 := F_VALIDATE_WITH_REGEXP ('a98sj183ydn3j4bg8du2l930spao12j4nj28fhr69aj27u37dh472h182o3g72o1','sha256');   
 IF v_res_sha256 THEN   
  DBMS_OUTPUT.PUT_LINE('SHA256 IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('SHA256 IS FALSE');   
 END IF;   
    
 v_res_onechar := F_VALIDATE_WITH_REGEXP ('a','onechar');    
 IF v_res_onechar THEN   
  DBMS_OUTPUT.PUT_LINE('ONECHAR IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('ONECHAR IS FALSE');   
 END IF;   
    
 v_res_phone := F_VALIDATE_WITH_REGEXP ('(0220) 555 98-02','phone');   
 IF v_res_phone THEN   
  DBMS_OUTPUT.PUT_LINE('PHONE IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('PHONE IS FALSE');   
 END IF;   
    
 v_res_taxinfo := F_VALIDATE_WITH_REGEXP ('J-000099998-2','taxinfo');    
 IF v_res_taxinfo THEN   
  DBMS_OUTPUT.PUT_LINE('TAXINFO IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('TAXINFO IS FALSE');   
 END IF;   
    
 v_res_url := F_VALIDATE_WITH_REGEXP ('https://www.google.com/search?q=enorable&oq=enorable','url');   
 IF v_res_url THEN   
  DBMS_OUTPUT.PUT_LINE('URL IS TRUE');   
 ELSE   
  DBMS_OUTPUT.PUT_LINE('URL IS FALSE');   
 END IF;   
   
END;

Statement processed.
ALPHANUMERIC IS TRUE
DATE1 (dd/mm/yyyy) IS TRUE
DATE2 (mm-dd-yyyy) IS TRUE
DATE3 (yyyy-mm-dd) IS TRUE
DATE4 (yyyy-mm) IS TRUE
DATE5 (yyyy) IS TRUE
DATETIME (yyyy-mm-dd hh:mm:ss) IS TRUE
DECIMAL IS TRUE
DOMAIN IS TRUE
EMAIL IS TRUE
HTTP IS TRUE
IMAGE IS TRUE
IPADDR IS TRUE
NUMERIC IS TRUE
MD5 IS TRUE
SHA1 IS TRUE
SHA256 IS TRUE
ONECHAR IS TRUE
PHONE IS TRUE
TAXINFO IS TRUE
URL IS TRUE

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database