Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

MS SQL column with fixed number of symbols

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading