How to create a CONSTRAINT CHECK in Sqlserver to validate versions with Regex

Asked

Viewed 824 times

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’s not working like this:

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

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

sql-server regex

1 answer

0

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 the CHECK as only accept if you are inside

Browser other questions tagged sql-server regex

You are not signed in. Login or sign up in order to post.