CODEWITHSUNDEEP
sqlregexoracle-databasevalidationif-statement
Beez
Beez

Reputation: 61

Oracle regular expression REGEXP_LIKE for multiple columns

I want to validate TAX , NAME, TELEPHONE NO . Those are should be alpha-numeric.So used this method but in here i want to know can i do this same by short method in simply way without using so many "AND" s. 

  IF REGEXP_LIKE(i.TAX, '^[A-Za-z0-9]+$') AND REGEXP_LIKE(i.NAME, '^[A-Za-z0-9]+$') AND REGEXP_LIKE(VarTelephone , '^[A-Za-z0-9]+$') THEN

      INSERT INTO BIZZXE_V2_SCH.SUPPLIER_CONTACTS
      (CONTACT_ID, SUPPLIER_ID, ADDRESS, ZIP_CODE, COUNTRY_ID, CITY_ID, TELEPHONE, FAX, EMAIL, XMLCOL)
      VALUES(VarContactId ,VarId, k.ADDRESS, k.ZIP_CODE, k.COUNTRY_ID, k.CITY_ID, k.TELEPHONE, k.FAX, k.EMAIL, VarContactDetail);

     ELSE 
       -- Start Return RVAL Messages --
       RVAL.EX_CODE:=1;
       RVAL.MESSAGE:='Supplier Tax and Supplier Name should be apha-numeric that is numbers letters and symbols..!';
       -- End Return RVAL Messages --
     END IF;

Upvotes: 0

Views: 5248

Answers (1)

Rodrigo López
Rodrigo López

Reputation: 4259

Since they all need to match the same Regex pattern, to be alphanumeric, you can validate on the concatenation of these 3 values:

IF REGEXP_LIKE(i.TAX || i.NAME || VarTelephone , '^[A-Za-z0-9]+$')

However this does not behave exactly as your question condition, since if just one or two of the values is empty, it would be evaluated true in this case and not in the other.

A possible workaround for that and in the case that you need different regex, for telephone verifying just digits ()- and + for instance, would be something like this:

IF REGEXP_LIKE(i.TAX || ' ' || i.NAME || ' ' || VarTelephone , 
    '^[A-Za-z0-9]+ [A-Za-z0-9]+ [\d()\-+]+$')

For this case all you have to make sure is that your chosen separator, in my case the space will never appear in any of the input texts.

Upvotes: 4

Related Questions