Is it possible to enforce data checking in MySQL using Regular Expression?

Yes, it is possible to enforce data checking in MySQL using regular expression. First, you need to create a table. After that you need to create a trigger before insert in table. Here, we will be checking the Phone Number format.

The query to create a table is as follows

mysql> create table enforceDataUsingRegularExpression - > ( - > yourPhoneNumber varchar(60) - > ); Query OK, 0 rows affected (0.59 sec)

The query to create a trigger is as follows

mysql> DELIMITER // mysql> CREATE TRIGGER enforce_phone_check BEFORE INSERT ON enforceDataUsingRegularExpression - > FOR EACH ROW - > BEGIN - > IF (NEW.yourPhoneNumber REGEXP '^(\+?[0-9]{1,4}-)?[0-9]{3,10}$' ) = 0 THEN - > SIGNAL SQLSTATE '33455' - > SET MESSAGE_TEXT = 'Your Phone Number is incorrect'; - > END IF; - > END // Query OK, 0 rows affected (0.16 sec) mysql> DELIMITER ;

Now look at the error when you insert record in the table to enforce data checking in MySQL using regular expression

mysql> insert into enforceDataUsingRegularExpression values("+55242-64576"); ERROR 1644 (33455): Your Phone Number is incorrect mysql> insert into enforceDataUsingRegularExpression values("+76-WD1232221"); ERROR 1644 (33455): Your Phone Number is incorrect Now inserting the correct data i.e. the correct phone, which is as follows: mysql> insert into enforceDataUsingRegularExpression values("+55-1232221"); Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from enforceDataUsingRegularExpression;

The following is the output

+-----------------+ | yourPhoneNumber | +-----------------+ | +55-1232221 | +-----------------+ 1 row in set (0.00 sec)