I searched the internet for 2 days to find the answer and couldn’t.
Basically I have this as a problem : "The login is composed of the first letter of the first name and the first 7 letters of the name (in lowercase) followed by two numbers."
My problem relies in the last 4 words "followed by two numbers". The first part I have done it correctly and it validates it, but i cant seem to find the answer to the problem specified above.
I tried doing something like this.
CONSTRAINT ck_user_login CHECK
(login LIKE SUBSTR(prenom,1, 1) || LOWER(SUBSTR(nom, 0, 7)) || '%[0-9]%')
But that does not seem to work.
All help will be appreciated. Thank you in advance.
>Solution :
I’d do it in two steps: first check first and last name, then check digits. Something like this:
SQL> CREATE TABLE test
2 (
3 prenom VARCHAR2 (10),
4 nom VARCHAR2 (10),
5 login VARCHAR2 (20)
6 );
Table created.
SQL> ALTER TABLE test
2 ADD CONSTRAINT ck_user_login CHECK
3 ( SUBSTR (login, 1, LENGTH (login) - 2) LIKE
4 SUBSTR (prenom, 1, 1) || LOWER (SUBSTR (nom, 1, 7))
5 AND REGEXP_LIKE (login, '\d{2}$'));
Table altered.
Testing:
SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23');
1 row created.
SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfootacd23');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfootacd23')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated
SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot235');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot235')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated
SQL> INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23x');
INSERT INTO test (prenom, nom, login) VALUES ('little', 'foot', 'lfoot23x')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_USER_LOGIN) violated
SQL>