SQL How to give a value a specific length-CodePudding

I currenly have a value for a code which is a varchar(45), but I want this code to only be exactly 5 characters long, which query with a constraint should I write to do this? I have tried what I have done down here, but it didn't work

ALTER TABLE `bigfivesafari`.`accommodatie` 
ADD INDEX `accommodatiecode` (`accommodatiecode`(5) ASC) VISIBLE;

CodePudding user response：

you can use this

LEN(myColumn) = 20

CodePudding user response：

Tested in MySQL 8.0.27:

mysql> create table accommodatie ( accommodatiecode varchar(45) 
          check (accommodatiecode regexp '^....[[:digit:]]$'));

mysql> insert into accommodatie set accommodatiecode = 'abc';
ERROR 3819 (HY000): Check constraint 'accommodatie_chk_1' is violated.

mysql> insert into accommodatie set accommodatiecode = 'abcde';
ERROR 3819 (HY000): Check constraint 'accommodatie_chk_1' is violated.

mysql> insert into accommodatie set accommodatiecode = 'abcd5';
Query OK, 1 row affected (0.01 sec)