-1

I'm trying to figure out how to add a CHECK constraint to the Version column to only accept NVARCHAR values with the format as such for example:

(Number.number.number)
10.4.1

or

(number.number.number. 3numbers)
10.4.1.111

This is my table:

CREATE TABLE dbo.Table
(
    ID int PRIMARY KEY IDENTITY,
    Version NVARCHAR(100) NOT NULL CHECK (Version)
)

Desired result would be, when a users updates the Version column, they must adhere to the defined formats.

For example, 11.3.4 would be acceptable and 12.4.3.444 would be acceptable.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
IeeTeY
  • 93
  • 7
  • I haven't been able to find anything, and I can't think of the logic on this myself or if it's even possible. So I'm just looking for some humble ideas. – IeeTeY Apr 13 '22 at 21:49
  • 2
    You would be better off doing the validation in the application layer; hopefully one that supports Regex. SQL Server has no such support for Regex. – Thom A Apr 13 '22 at 21:50
  • https://stackoverflow.com/questions/29959218/check-constraint-for-string-with-a-format-in-sql-server – Dale K Apr 13 '22 at 21:56

1 Answers1

0

Please try the following solution.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo._tbl;

CREATE TABLE dbo._tbl (ID INT IDENTITY PRIMARY KEY, ver VARCHAR(30));
INSERT INTO dbo._tbl (ver) VALUES
('10.4.1'),
('10.4.1.111');

alter table dbo._tbl
add constraint codeCheck check (COALESCE(TRY_CAST(PARSENAME(ver, 1) AS INT) 
    + TRY_CAST(PARSENAME(ver, 2) AS INT) 
    + TRY_CAST(PARSENAME(ver, 3) AS INT), 0) + COALESCE(TRY_CAST(PARSENAME(ver, 1) AS INT)
    + TRY_CAST(PARSENAME(ver, 2) AS INT) 
    + TRY_CAST(PARSENAME(ver, 3) AS INT) 
     + TRY_CAST(PARSENAME(ver, 4) AS INT), 0) > 0);

-- it will fail
INSERT INTO dbo._tbl (ver) VALUES
('10.4.r');

-- it will work
INSERT INTO dbo._tbl (ver) VALUES
('17.7.77');

SELECT * FROM dbo._tbl;
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21