• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

In MySQL column, how do I only allow data that matches a certain pattern?

I am using a current version of MySQL

I have created a table:

CREATE  TABLE IF NOT EXISTS `Uniq_Entity` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ein` VARCHAR (50) NULL  ,
  UNIQUE KEY `uq_ein` (ein)
)
ENGINE = InnoDB;

I just was asked to prevent the ein field from having any value other than this pattern:

[0-9a-zA-Z]{18}[0-9]{2}

We are importing the data from a csv file using LOAD DATA LOCAL INFILE .

How do I modify the CREATE TABLE so any attempt to INSERT or UPDATE ein with data that does not match this pattern gets rejected?

Alternatively, how would I modify the INSERT or UPDATE query that would ignore any field that fails to match?

Thanks in advance!
0
John_2357
Asked:
John_2357
1 Solution
 
lwadwellCommented:
If you want to ignore the non-complaint rows for the load from file only, consider loading the file into a staging table then copy the rows from that table into the final table
    WHERE ein REGEXP '^[0-9a-zA-Z]{18}[0-9]{2}$'


If you are looking for a permanent validation, you could use a trigger on the table, e.g.
CREATE TABLE IF NOT EXISTS `Uniq_Entity` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `ein` VARCHAR (50) NULL  ,
  UNIQUE KEY `uq_ein` (ein)
)
ENGINE = InnoDB;

CREATE TRIGGER Uniq_Entity_itrig 
BEFORE INSERT ON Uniq_Entity
FOR EACH ROW
BEGIN
  IF new.ein NOT REGEXP '^[0-9a-zA-Z]{18}[0-9]{2}$' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid ein value';
  END IF;
END;

insert into Uniq_Entity (`ein`) values 
('12345678901234567123');
insert into Uniq_Entity (`ein`) values 
('abcdefghijklmnopq987');
insert into Uniq_Entity (`ein`) values 
('1');
insert into Uniq_Entity (`ein`) values 
('12345@@@901234567123');

Open in new window

The trigger will generate errors in INSERT of an invalid value.  Another trigger would be required for 'BEFORE UPDATE ON'.
0
 
John_2357Author Commented:
thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now