VARCHAR contains only characters: how to do ?

[begueradj]

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

[hfx642]

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.

[begueradj]

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

[hfx642]

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

[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.

[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;