how to Allow only alpha numeric values in mysql

Question

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');

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.

'Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:' - dev.mysql.com/doc/refman/8.0/en/… . Either do this check in your front end or in a trigger. — P.Salmon
– P.Salmon, Commented Jul 25, 2019 at 10:09

James · Accepted Answer · 2019-07-25 08:30:42Z

1

Assuming it's MySQL >=8.0.16

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

answered Jul 25, 2019 at 8:30

James

1,8292 gold badges11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joe Over a year ago

numeric values are also inserting

James Over a year ago

since you are using version less than 8.0.16 it won't work

joe Over a year ago

,i have upgraded to 8.0.17 its working, how to add error message for this please let me know like Empid should be ab123/ab1234/a12345 format

James Over a year ago

It's quite a bit long, you need to use stored procedure to capture the error. There might be other solution too but you can't just do using only query. So where you need to show those error? If its in app side you can do it there.

Collectives™ on Stack Overflow

how to Allow only alpha numeric values in mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related