Member Avatar for Member #153709

Hello people,

I declared a field in my table : my_field VARCHAR(20), but I don't want it to contain any number: can you tell me how I can do that using a trigger or using a constraint ?

Thank you in advance for any help

  • 4 Contributors
  • 5 Replies
  • 229 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by mohamedsayed

Dani AI

Generated

The cleanest, most maintainable solution is a declarative CHECK constraint rather than a row‑trigger loop. A CHECK keeps the rule visible in the schema, is enforced by the engine for INSERT/UPDATE, and avoids hidden PL/SQL logic that’s easy to forget — this is why constraints are the recommended first line of defense. This ties together the trigger suggestion from and and the constraint suggestion from : use a constraint when the rule is expressible in SQL. (docs.oracle.com)

A compact, readable pattern is to use Oracle’s REGEXP_LIKE to forbid any digit. Example alternatives (pick one to fit your style):

-- inline at table creation
my_field VARCHAR2(20) CHECK (REGEXP_LIKE(my_field, '^[^[:digit:]]*$'))

-- or add to an existing table
ALTER TABLE your_table
  ADD CONSTRAINT chk_no_digits
  CHECK (NOT REGEXP_LIKE(TRIM(my_field), '[[:digit:]]'));

REGEXP_LIKE is allowed inside CHECK constraints and is a simple way to express “no digits”. Use TRIM() in the check if you want trailing/leading spaces ignored. (docs.oracle.com)

Important gotchas and tips: Oracle treats a zero‑length string as NULL, so an empty value does not violate a CHECK expression that only looks for digits — add NOT NULL if empty strings must be forbidden. When adding a new constraint to a table with existing rows, use ENABLE NOVALIDATE to enforce it only for new/changed rows while you clean historical data. Also remember CHECK conditions cannot call user‑defined PL/SQL functions (only deterministic SQL expressions). Finally, in Oracle prefer VARCHAR2 (not VARCHAR) for variable‑length text columns. (docs.oracle.com)

Summary: prefer a CHECK + REGEXP_LIKE for this simple rule, use NOT NULL if you must ban blanks, and use ENABLE NOVALIDATE when applying to populated tables to avoid immediate failures.

Recommended Answers

All 5 Replies

Member Avatar for Member #647493

The easiest way is to control this by the application that will be inserting and/or updating the column.
ie. Oracle Forms???

A more difficult way (but not too difficult) is to use a Before Insert trigger.
Loop through your digits (0..9)
and do an InStr() on your input string, checking for your digits.
Fail the trigger if you find any numbers.

Edited by Member #647493 because: n/a
Member Avatar for Member #153709

The easiest way is to control this by the application that will be inserting and/or updating the column.
ie. Oracle Forms???

A more difficult way (but not too difficult) is to use a Before Insert trigger.
Loop through your digits (0..9)
and do an InStr() on your input string, checking for your digits.
Fail the trigger if you find any numbers.

Thank you very much for your help, I used the trigger as you advised me

Member Avatar for Member #647493

Please mark this thread as "solved".
Thank-You!

Member Avatar for rotten69

Or you can put a constraint on the field to restrict the input. In your case, you'd check if the input contains numbers, if yes. then it won't be inserted.

ALTER TABLE TableName ADD CONSTRAINT ConstraintName CHECK(YourField........  )

Instead of the dots up there, you replace them with a function or something that checks if the input is CHAR or is not INT.

Member Avatar for mohamedsayed

create or replace trigger t2
before insert on table_name
for each row
declare
v_name varchar2(20);
begin
for i in 0..9 loop
if instr(:new.column_name,i)>0 then
Raise_application_error(-20001,'you can not insert
numeric datatype in varchar datatype');
end if;
end loop;
end;

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.