MsSQL检查约束的正则表达式 [英] Regex for MsSQL check constraint

问题描述

我正在尝试对我拥有的表进行列级检查约束.我希望检查插入列中的值，并确保它们只是字符.

I am trying to make a column level check constraint on a table I have. I want values being inserted into the columns to be checked and made sure they're only character.

例如，值('hello'，'there')会通过，但值('h3llo'，'th3r3')不会通过.

For example values ('hello','there') would pass, but values ('h3llo','th3r3') would not.

我可以让它在一定数量的字符上工作(请参见下表)，但我想使其动态化.

I can get it to work for a specific amount of characters (see table below), but I want to make it dynamic.

我也尝试过 ^ [a-zA-Z] + $ ，但这似乎也不起作用.

I have tried ^[a-zA-Z]+$ as well, but that doesn't seem to work either.

简单的表格布局如下.

CREATE TABLE owner
  (
     owner_id        ID IDENTITY(1, 1) PRIMARY KEY,
     owner_firstname FIRSTNAME,
     owner_lastname  LASTNAME,
     CONSTRAINT firstname_cc CHECK (owner_firstname LIKE '[a-zA-Z][a-zA-Z][a-zA-Z]'),
     CONSTRAINT lastname_cc CHECK (owner_lastname LIKE '[a-zA-Z][a-zA-Z][a-zA-Z]')
  ); 

推荐答案

SQL Server LIKE 语法不接受正则表达式.

SQL Server LIKE syntax does not accept regular expressions.

您可以使用 check(owner_firstname不类似于'％[^ A-Z]％').

此约束将拒绝任何包含字符的值，这些字符的字符不在 A-Z 范围内.

This constraint rejects any value containing a character not in the range A-Z.

除非您使用区分大小写的排序规则，否则无需指定 a-z .

You don't need to also specify a-z except if you are on a case sensitive collation.

这篇关于MsSQL检查约束的正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！