datatypes wwithout spaces?

[rlowe]

All -

I have a column in which I need to disallow spaces, punctuation,numbers, etc... (limited to [A-z]). Is there some obscure datatype that I can use, or will I be forced to do it through a trigger?

Thanks,

R

[pipo]

I guess you'll have to use a trigger ...

[molonede]

You could use check constraint if the column is one character.

alter table TEST add constraint CHECK_TEST
check (mycolumn between 'a' and 'z' or mycolumn between 'A' and 'Z') ;

otherwise you will need a trigger.

[ocp2b]

I believe you could also do a check constraint on the column.
Something like (assuming the table already exists): Alter table TBLE add constraint col1-constraint CHECK (col1 in ('A','B','C','D',...'z')).

[rlowe]

Sorry, I was unclear. The column is 12 characters long, so I cannot check constraint...
R

[rlowe]

Sorry, I was unclear. The column is 12 characters long, so I cannot check constraint...
R

[ocp2b]

Oh! That makes a big difference. I'm not that knowledgable of Oracle but I love learning this stuff. The only other thing that I can think of would be to create a stored procedure/function that will read in the field and then loop through each "cell" and verify if it is [A-z]. Is it possible to have trigger call a stored procedure? Please post how you are able to resolve this issue. I am interested.

[Knarayan]

I had to check for non alpha numeric numbers but this was while loading from one table to another. I created a function like this:
create or replace FUNCTION Alcheck (word IN VARCHAR2) RETURN
NUMBER DETERMINISTIC AS
LN NUMBER;
chk VARCHAR2(10);

BEGIN
SELECT TRANSLATE(UPPER(word),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()<>?/\|{}[]-+~`_-=',
'9999999999ABCDEFGHIJKLMNOPQRSTUVWXYZxxxxxxxxxxxxxxxxxxxxxxxxxxx') "License"
INTO chk
FROM DUAL;
LN:=LENGTH(chk);
FOR i IN 1..LN LOOP
IF SUBSTR(chk,i,1)='x' THEN
RETURN 1;
EXIT;
END IF;
END LOOP;
RETURN 0;
END;
so select alcheck('ald') from dual
would return me 0.
I think you could use a funcion like this in the trigger before insert.

HTH.

[sambavan]

This would be most appropriate under development forum rather than in the administration forum. So, I'm moving it to the development forum

Sam

[jmodic]

You don't need any trigger or procedure etc, a simple check constraint using TRANSLATE() will do.

ALTER TABLE my_table ADD CONSTRAINT check_for_non_alpha
CHECK (TRANSLATE(UPPER(alpha_column), '1ABCD....XYZ', '1') IS NULL);