CHECK with ^[A-Z]{3}[0-9]{6}$ - SQL Server

Question

CREATE TABLE PARTICIPANTE(
    pasaporte NVARCHAR(9) NOT NULL,
    nombre NVARCHAR(50) NOT NULL,
    sexo CHAR(1) NOT NULL,
    fecNac DATE NOT NULL,
    codPais NVARCHAR(3) NOT NULL,
    CONSTRAINT PK_PARTICIPANTE PRIMARY KEY (pasaporte),
    CONSTRAINT FK_PAIS_PARTICIPANTE FOREIGN KEY (codPais) REFERENCES PAIS(codigo),
    CONSTRAINT CHK_PASAPORTE CHECK (pasaporte like '^\[A-Z\]{3}\[0-9\]{6}$')
)

The CONSTRAINT CHK_PASAPORTE doesn't work when I try to insert the data.

The INSERT statement conflicted with the CHECK constraint "CHK_PASAPORTE". The conflict occurred in database "OMA", table "dbo.PARTICIPANTE", column 'pasaporte'.

Example

insert into PARTICIPANTE (pasaporte,nombre,sexo,fecNac,codPais) value ('JPN865653','Noguchi','F','20000104','JPN');

Can someone explain to me why this doesn't work and how can I fix it?

SQL Server doesn't support Regex, so your `LIKE` expression isn't doing what you *think* it is. Your pattern would only permit strings that start with `^\ `, then have a letter or reverse solidus (`\ `) , then have `{3}\ `, followed by a digit or reverse solidus, and then finally `{6}$` (ignore trailing spaces due to markdown limitations). — Thom A, Nov 04 '22 at 12:06
Anyway, the regex is wrong, it must be `'^[A-Z]{3}[0-9]{6}$'` — Wiktor Stribiżew, Nov 04 '22 at 12:06
So, for example, the value `'^\H{3}\7{6}$'` would be acceptable, as would `'^\\{3}\\{6}$'`. — Thom A, Nov 04 '22 at 12:11

score 2 · Answer 1 · answered Nov 04 '22 at 12:25

As I mention in the comments, SQL Server has no (in built) support for Regex, it only has basic pattern matching, which is explained in the documentation.

Fortunately, the logic you are after appears to be quite simple; 3 letters followed by 6 digits. This can be achieved with the following constraint:

ALTER TABLE dbo.PARTICIPANTE ADD CONSTRAINT CHK_PASAPORTE CHECK (pasaporte LIKE '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]');

Note that if you require the value to only contain uppercase values, you'll need to COLLATE the value to a collation that is case sensitive and orders upper case letters first, then lowercase, and finally alphabetically (Binary collations are one such one that does this).

CHECK with ^[A-Z]{3}[0-9]{6}$ - SQL Server

1 Answers1