MS SQL column with fixed number of symbols-CodePudding

I'm using MS SQL and creating a table. One of the columns should contain a string with 4 symbols: L001, L002 There cannot be less or more symbols, e.g. L01 and L0001 are errors.

Which type of data I need to use? CHAR(4) and NCHAR(4) allow me to create L01 and it's wrong in my case.

CodePudding user response：

You should to use CHECK CONSTRAINT: It possible to create table with constraint

CREATE TABLE Test (
    ID int NOT NULL,
    Code varchar(4),
    CHECK (LEN(Code) = 4)
);

or update exist table:

CREATE TABLE Test (
    ID INT NOT NULL,
    Code CHAR(4)
);

ALTER TABLE Test 
   ADD CONSTRAINT CHK_Code_LEN   
   CHECK (LEN(Code) = 4);

Test:

INSERT INTO Test VALUES (1, 'L001'); -- data inserted

INSERT INTO Test VALUES (2, 'L0011'); -- error raised

INSERT INTO Test VALUES (2, 'L11'); -- error raised

Result:

SELECT * FROM Test;

 ==== ====== 
| ID | Code |
 ==== ====== 
| 1  | L001 |
 ---- ------

MS SQL fiddle CHECK