SQL Server supports regular expressions when checking constraints, triggers are not always needed

Someone asked this question:

I need to create a group table with a code column. Can I add a restriction on a column that would allow only alphabetic characters (D, M, O, P, or T) followed by two numeric characters?

They answered him:

You cannot do this in the usual way - MS SQL Server supports checking constraints, but only in the form of min / max values ​​of INT, string length, etc. What you need is a regular expression test that SQL Server does not support. Theoretically, you can make .NET insertion, deploy to SQL Server and force it to use such a check - a non-trivial task.

Let's try to figure it out

Despite the fact that the SQL server does not fully support regular expressions, the above task can be solved without any problems using T-SQL. This is what this regular expression will look like.
')
[DMOPT][0-9][0-9]

It only allows alphabetic characters (D, M, O, P or T) followed by two numeric characters. Okay, enough talk, let's go to the code

Create a table

 CREATE TABLE blatest(code char(3)) 

Add constraint check

 ALTER TABLE blatest ADD CONSTRAINT ck_bla CHECK (code like '[DMOPT][0-9][0-9]' ) GO 

Now a few instructions for inserting data

 INSERT blatest VALUES('a12') --  INSERT blatest VALUES('M12') --  INSERT blatest VALUES('D12') --  INSERT blatest VALUES('DA1') --  

As you can see, we received an error message twice.
547, 16, 0, 1
INSERT CHECK "ck_bla". "Test", "dbo.blatest", 'code'.
.

If you want to insert D12, not d12, i.e. need case sensitivity, then you need to create such a restriction

 (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) 

All that was needed was to add the SQL_Latin1_General_CP1_CS_AS sort parameter.

To find out what this parameter gives, run the following query.

 SELECT * FROM ::fn_helpcollations() WHERE name = 'SQL_Latin1_General_CP1_CS_AS' 

Here is what the query returned in the description

Latin1-General, case-sensitive, accent-sensitive, kanatype-
insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

Let's create a new constraint, but first we need to remove the old one.

 ALTER TABLE blatest DROP CONSTRAINt ck_bla GO 

Now create a new and check

 ALTER TABLE blatest ADD CONSTRAINT ck_bla CHECK (code LIKE '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) GO INSERT blatest VALUES('D12') --  INSERT blatest VALUES('d12') --  

Inserting the value of D12 will succeed, but d12 will not.
As you can see, the use of regular expressions in constraints is possible and no trigger is needed in this case.