Regular expression in PL/SQL

Question

Hi All,

I am using a regular expression to find out whether the user entered value is alpha numeric, allowing some special characters.

I am using the following code which works fine:

CREATE OR REPLACE PROCEDURE Validate_Inputstring (input_String IN VARCHAR2) AS
BEGIN

  IF REGEXP_LIKE(input_String,'^[A-Z0-9a-z,+-?@]*$') THEN 
      DBMS_OUTPUT.PUT_LINE('U have entered alphanumeric chars--->'|| input_String);
    ELSE
      DBMS_OUTPUT.PUT_LINE('U NOT have entered alphanumeric chars---->'|| input_String);
  END IF;
END;

The above programs works fine.
Now my poblem is these special symbols are dynamic and these values vary upon the application.

In other words, these are stored in the database and different for each application.

For example, for application A these might be , + - ? @, for B these might be { ^ & '.

Is it possible to write a regular expression such that it checks for alphanumeric chars and these dynamic special symbols at a time?

I have tried something like

CREATE OR REPLACE PROCEDURE Validate_Inputstring (input_String IN VARCHAR2) AS
    special_symbols     VARCHAR2(300);

BEGIN
    special_symbols :=',+-?';
    IF  REGEXP_LIKE(input_String,'^[A-Z0-9a-zspecial_symbols]*$') THEN 
        DBMS_OUTPUT.PUT_LINE('U have entered alphanumeric chars--->'|| input_String);
      ELSE
        DBMS_OUTPUT.PUT_LINE('U NOT have entered alphanumeric chars---->'||    input_String);
    END IF;
END;

but it doesn't work. Does anyone have a solution for this? Thanks in advance!

Answer 1

REGEXP_LIKE(input_String,'^[A-Z0-9a-zspecial_symbols]*$')

You have to break out of the string to interpolate your symbols:

REGEXP_LIKE(input_String,'^[A-Z0-9a-z'+special_symbols+']*$')

However there's a bit of a problem in that your special symbols might also be special to regex. A ‘-’ in a []-character-class means do a range instead of literally matching ‘-’; a ‘]’ would prematurely end the character-class. You'd have to backslash-escape these characters, along with backslash itself, using a string-replace.

special_symbols:= ',+-?';
special_group:= replace(replace(replace('\', '\\'), '-', '\-'), ']', '\]');
IF REGEXP_LIKE(input_String,'^[A-Z0-9a-z'+special_group+']*$') ...

Answer 2

Thanks for your response

I have code as below FUNCTION Validate_Inputstring_Func(input_String IN VARCHAR2) RETURN BOOLEAN IS

special_symbols VARCHAR2(300); BEGIN

special_symbols :='+?@'; DBMS_OUTPUT.PUT_LINE('Enetered value is NULL'); ELSE DBMS_OUTPUT.PUT_LINE('Enetered value is Not NULL'|| input_String);

IF REGEXP_LIKE(input_String,'^[A-Z0-9a-z'+special_symbols+']*$') THEN DBMS_OUTPUT.PUT_LINE('U have entered alphanumeric chars--->'|| input_String); RETURN false;

ELSE DBMS_OUTPUT.PUT_LINE('U NOT have entered alphanumeric chars---->'|| input_String); RETURN true; END IF; END IF;

END;

Answer 3

bobince was on the right track but the syntax wasn't quite right - the concatenation operator in Oracle is ||, and there was a missing expression in the replace() calls...

REGEXP_LIKE(input_String,'^[A-Z0-9a-zspecial_symbols]*$')

You have to break out of the string to interpolate your symbols:

REGEXP_LIKE(input_String,'^[A-Z0-9a-z' || special_symbols || ']*$')

However there's a bit of a problem in that your special symbols might also be special to regex. A ‘-’ in a []-character-class means do a range instead of literally matching ‘-’; a ‘]’ would prematurely end the character-class. You'd have to backslash-escape these characters, along with backslash itself, using a string-replace.

special_symbols:= ',+-?';
special_group:= replace(replace(replace(special_symbols, '\', '\\'), '-', '\-'), ']', '\]');
IF REGEXP_LIKE(input_String,'^[A-Z0-9a-z' || special_group || ']*$') ...