CODEWITHSUNDEEP
sqlregexoracle-databaseoracle12c
T.Poe
T.Poe

Reputation: 2089

Check if string contains certain characters

I'm trying to check if the login being inserted into employee table is given in a certain format, which is: the first letter is strictly given - x, then 5 letters and two numbers => xlogin00.

CREATE OR REPLACE TRIGGER check_login
BEFORE INSERT OR UPDATE OF login ON employee
FOR EACH ROW
DECLARE
    xlogin00 employee.login%TYPE;
    prefix VARCHAR2(1);
    name VARCHAR2(5);
    number VARCHAR2(2);
BEGIN
xlogin00 := :new.login;
prefix := substr(xlogin00,1,1);
name := substr(xlogin00,2,5);
number := substr(xlogin00,7,2);
if(LENGTH(xlogin00) != 8) then
    Raise_Application_Error (-20203, 'Error');
end if;
if(prefix != 'x') then
    Raise_Application_Error (-20204, 'Error');
end if;
if NOT REGEXP_LIKE(name, '[^a-z]') then
    Raise_Application_Error (-20204, 'Error');
end if;
if NOT REGEXP_LIKE(number, '[^0-9]') then
    Raise_Application_Error (-20204, 'Error');
end if;
END;

The only thing that seems to work in my code is if(prefix != 'x'). Other stuff doesn't work at all. What am I doing wrong?

Upvotes: 0

Views: 4579

Answers (1)

trincot
trincot

Reputation: 351084

You can do all of that with one check:

IF NOT REGEXP_LIKE(:new.login, '^x[a-z]{5}\d\d$') THEN
    Raise_Application_Error (-20204, 'Error');

Note that you had also your boolean logic reversed: in both last tests you have a double negation (NOT in the IF and [^...] in the regex).

Note that in the proposed regex the ^ and $ are needed to make sure the match is with the complete login value, not just a substring.

Upvotes: 2

Related Questions