Using ‘LIKE’ and ‘REGEXP’ in a SQL query

I’m trying to use some regex on an expression where I have two conditions on the WHERE clause. The pattern I want to capture is 106 followed by any digit followed by a digit that must be either 3 or 4, i.e. 106[0-9][3-4]

First, I tried this:

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND (ForestID REGEXP '106[0-9][3-4]') 

This produced an error as below and it would be good to know why.

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near 'REGEXP'.

Next, I have tried this, which is now running but I am unsure about whether this is doing what I want it to do.

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND (ForestID LIKE '106[0-9][3-4]') 

Would this do as I described above?

Answer

You do not need to interact with managed code, as you can use LIKE:

SELECT DISTINCT Loggers
FROM [alo].[Forests] C
WHERE (R.LogSU = 3)
AND ForestID LIKE '106[0-9][3-4]')

to make clear: SQL Server doesn’t supports regular expressions without managed code. Depending on the situation, the LIKE operator can be an option, but it lacks the flexibility that regular expressions provides.

If you would like to have full regular expression functionality, try this.