| CHECK CONSTRAINT - HARD FOR ME, EASY FOR YOU [message #374284] |
Mon, 04 June 2001 10:31  |
LEVIATHAN
Messages: 8
Registered: June 2001
|
Junior Member |
|
|
I'M NEW TO SQL ORACLE, I HAVE 2 PROBLEMS
FIRSTLY, IS THERE A WAY WHEN CREATING A DATA TYPE SO THAT THE FIRST 3 CHARACTERS ARE NUMBER AND THE LAST 3 CHARCTERS ARE LETTERS, WITHOUT EXCEPTIONS.
IE. 999BUS
SECONDLY I WANT TO CREATE A CONSTRAINT SO THAT A DATE CAN ONLY BE AFTER A CERTAIN DATE AND BEFORE THE PRESENT DATE. I SUSPECT IT USES CHECK BUT I CANT QUITE GET IT RIGHT.
|
|
|
|
| Re: CHECK CONSTRAINT - HARD FOR ME, EASY FOR YOU [message #374287 is a reply to message #374284] |
Mon, 04 June 2001 12:13  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
You could do this with a trigger too, but try something like this:
alter table ABC add constraint CK_ABC_COL1 check
(
substr(COL1, 1, 1) <= '9' and
substr(COL1, 1, 1) >= '0' and
substr(COL1, 2, 1) <= '9' and
substr(COL1, 2, 1) >= '0' and
substr(COL1, 3, 1) <= '9' and
substr(COL1, 3, 1) >= '0' and
substr(COL1, length(COL1) - 1, 1) <= 'Z'
substr(COL1, length(COL1) - 1, 1) >= 'A'
substr(COL1, length(COL1) - 2, 1) <= 'Z'
substr(COL1, length(COL1) - 2, 1) >= 'A'
etc...
);
You could also create a function to check that a part of a string is numeric and use that to avoid the repetitive substr checks for each character.
CREATE OR REPLACE FUNCTION to_num (v_value IN VARCHAR2)
RETURN NUMBER
IS
v_retval NUMBER;
BEGIN
v_retval := TO_NUMBER (v_value);-- try to convert it!
RETURN v_retval;-- return the number!
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;-- cant convert!
END to_num;
/
then ...
nvl(to_num(substr(COL1, 1, 3)), 123) = substr(COL1, 1, 3)
Make sure that you have a not null constraint too
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
