How to restrict an Oracle apex item to numeric/character values

I have a TEXT item field - order number, where user can enter his details.

When a user enters order number starting with 1, it should allow only numeric values.
When a user enters order number starting with L, it should accept both numeric and alphabets.

How can I do it in Oracle Apex?

Solution

You could use a CHECK constraint on the column to allow:

values with only digits when starting with '1'
values as it is when starting with 'L'

Check constraint would be:

CHECK
(
  CASE
  WHEN SUBSTR(text,1,1) ='1' THEN
    REGEXP_substr(text, '^[[:digit:]]+$')
  WHEN SUBSTR(text,1,1) ='L' THEN
    text
  END IS NOT NULL)

Let's look at a test case:

Setup

SQL> CREATE TABLE t(text VARCHAR2(30));

Table created.

SQL>
SQL> ALTER TABLE t ADD CONSTRAINT t_chk CHECK
  2  (
  3    CASE
  4    WHEN SUBSTR(text,1,1) ='1' THEN
  5      REGEXP_substr(text, '^[[:digit:]]+$')
  6    WHEN SUBSTR(text,1,1) ='L' THEN
  7      text
  8    END IS NOT NULL);

Table altered.

SQL>

Test

SQL> INSERT INTO t VALUES('123');

1 row created.

SQL> INSERT INTO t VALUES('1a');
INSERT INTO t VALUES('1a')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated


SQL> INSERT INTO t VALUES('L12A');

1 row created.

SQL> INSERT INTO t VALUES('A12A');
INSERT INTO t VALUES('A12A')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.T_CHK) violated


SQL> SELECT * FROM t;

TEXT
------------------------------
123
L12A

SQL>

So, it only allowed values '123' and 'L12A'. And it didn't allow values '1a' and 'A12A'. Works perfectly!