How to create a CONSTRAINT CHECK in SQLServer to validate versions with RegEx

Question

How to create a CONSTRAINT CHECK in SQLServer to validate versions with RegEx

Navigation

#1 by (0 votes)

0

Hello, I am creating a table in SQLServer where I want to validate a field to only accept the correct version mask, like this:

CREATE TABLE Versao (
    id INT NOT NULL IDENTITY(1,1),
    codigo NVARCHAR(100) NOT NULL,

    CONSTRAINT [PK_Versao] PRIMARY KEY (id),
    CONSTRAINT [CK_Versao_codigo] CHECK (codigo LIKE '%[0-9]+.[0-9]+.[0-9]+.[0-9]+%')
);

My problem is when I try to insert a record but it is not working, like this:

INSERT INTO Versao (codigo) VALUES ('0.123.0198651687.1');

Theoretically, based on this test I did on the site RegExr the regex would validate if the used code contains only numbers and has 4 blocks with 3 points between them, So why does not my SQL Server want to accept the insert?

sql-server regex

asked by anonymous 18.04.2018 / 14:28

1 answer

Copy code snippet, modify and paste into another Div Is it possible to restart apache using whm?

score 0 · Answer 1

I found the solution right after posting (rsrs), here it is:

CREATE TABLE Versao (
    id INT NOT NULL IDENTITY(1,1),
    codigo NVARCHAR(100) NOT NULL,
    --
    CONSTRAINT [PK_Versao] PRIMARY KEY (id),
    CONSTRAINT [CK_Versao_codigo] CHECK (codigo LIKE '[0-9]%.[0-9]%.[0-9]%.[0-9]%')
);

CREATE INDEX [UX_Versao_codigo] ON Versao(codigo);

INSERT INTO Versao(codigo) VALUES ('0.0.0.1'), ('1230.0.123120.1');

SELECT * FROM Versao

My problem is that I was understanding CHECK as only accepting if it is inside