CODEWITHSUNDEEP
sqlsql-serverregex
PedroWantun
PedroWantun

Reputation: 11

Regex constraints in SQL Server

I've been trying to add a constraint to one of my tables where the contents followed a specific structure (int-int;int-int;int-int...) with an undefined length. I've created the below regex script, which I've checked and does work in PostgreSQL, but I can't make it work for Microsoft's SQL Server, and I'd need some guidance as to what I'm doing wrong.

I've read the documentation and seen that MS SQL Server's LIKE clause is limited, but I don't know how to work around this.

ALTER TABLE ActionRanges
ADD CONSTRAINT StructureCheck
CHECK ValidRanges LIKE '^([0-9]+[-][0-9]+[;]){0,}([0-9]+[-][0-9]+)'

Upvotes: 1

Views: 257

Answers (1)

Stuck at 1337
Stuck at 1337

Reputation: 2084

SQL Server doesn't support RegEx natively; you'd need some CLR implementation (and that won't work in all flavors of SQL Server or with stricter security settings).

I don't know that you'll have a good time trying to implement this functionality in a constraint - maybe with a function but that's an extra layer of abstraction that probably defeats the use of a constraint in the first place.

Depending on your version of SQL Server, you could easily examine each element in the string using STRING_SPLIT, and enforce it in a trigger. e.g.

CREATE TRIGGER dbo.BudgetRegExSimulation
ON dbo.myTable
FOR INSERT, UPDATE
AS
BEGIN
  IF @@ROWCOUNT > 0 AND UPDATE(myCol) AND EXISTS
  (
    SELECT 1 FROM inserted AS i
    CROSS APPLY STRING_SPLIT(i.myCol, ';') AS s
    CROSS APPLY STRING_SPLIT(s.value, '-') AS d
    WHERE 
      TRY_CONVERT(int, d.value) IS NULL
    --^^-- either side of the individual element is not int
      OR i.MyCol NOT LIKE '[0-9]%[0-9]'
    --^^-- entire string starts or ends with not a digit
      OR LEN(s.value) - LEN(REPLACE(s.value, '-', '')) > 1
    --^^-- element contains more than one dash
  )
  BEGIN
    RAISERROR('Tsk tsk!', 11, 1);
    ROLLBACK;
  END
END
GO

Working example in this fiddle.

Or you could force all writes to go through a data access layer that calls a stored procedure that does this.

Or force the RegEx checks in the application, where you can do it natively, before bothering SQL Server with a write you could have predicted it would reject.

Upvotes: 1

Related Questions