Oracle regular expression REGEXP_LIKE for multiple columns

Question

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;

Rodrigo López · Accepted Answer · 2015-06-13 13:18:25Z

4

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.

edited Jun 13, 2015 at 13:18

answered Jun 13, 2015 at 5:46

Rodrigo López

4,3051 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

Although this answers the question, I think it is a bad approach. The OP should check each value individually and return an error message specific to the columns with problems. I also find it curious that a telephone number would have letters, but not '+', '(', ')', and '-'.

Rodrigo López Over a year ago

The first answer had a case were it would not behave just like question, edited to address this situation and also if you need a different regex validation, please check

Collectives™ on Stack Overflow

Oracle regular expression REGEXP_LIKE for multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related