CODEWITHSUNDEEP
mysql
joe
joe

Reputation: 73

how to Allow only alpha numeric values in mysql

I have a table

CREATE TABLE table_name
(
    EmpId VARCHAR(50),
    Name VARCHAR(100)
)

How can I restrict the EmpId column to consist of two letters followed by 3-5 digits? The following are all examples of valid values:

ac236, ak2356, av23695, ak365

I tried using the following check constraint:

ALTER TABLE table_name
ADD CONSTRAINT ck_table_name CHECK (EmpId NOT LIKE'%[^a-zA-Z0-9 ]%')

However, it allows all combinations of letters & digits, such as "23" and "fads":

INSERT INTO table_name
  VALUES
('23', 'Test 2'),
('fabs', 'Test 2');

inserted rows with invalid EmpIDs

If a value violates the format, I'd like the query to fail and print error message. For example, if 'na23' were inserted as the EmpID, MySQL could say:

Empid should be ab123/ab1234/a12345 format

Initially, I was using MySQL 5.7.11-0ubuntu6-log (which, it turns out, doesn't support CHECK constraints), but have upgraded to MySQL 8.0.17.

Upvotes: 1

Views: 1555

Answers (1)

James
James

Reputation: 1829

Assuming it's MySQL >=8.0.16

check(EmpId regexp '^[a-z]{2}[0-9]{3,5}$')

Upvotes: 1

Related Questions