Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
274 Views
Advertisements