CODEWITHSUNDEEP
mysqlregexmysql-regexp
Diego Gamboa
Diego Gamboa

Reputation: 43

How to compare a parameter of a Stored procedure with a REGEXP

I have a stored Procedure that I want to use for validations in the tables, and I have a regexp that I need to compare one of the parameters, how can I compare the regexp with my parameter. I tried this:

SELECT id REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$'

id is the parameter that I receive, this is all the stored procedure:

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
))  
BEGIN  
    IF NOT (SELECT id REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error';
    ELSE IF (changeDate > NOW()) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error'; 
    END IF;
END &&  
DELIMITER ;

Upvotes: 0

Views: 150

Answers (1)

Barmar
Barmar

Reputation: 782148

You don't need to use the subquery.

You have an extra ) after the parameter list.

ELSE IF needs to be one word ELSEIF.

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
)
BEGIN  
    IF id NOT REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error in id';
    ELSEIF changeDate > NOW() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error in changeDate'; 
    END IF;
END &&  
DELIMITER ;

Upvotes: 1

Related Questions