Specify the number of characters that should match a LIKE REGEX in T-SQL

Question

I've done a ton of Googling on this and can't find the answer. Or, at least, not the answer I am hoping to find. I am attempting to convert a REGEXP_SUBSTR search from Teradata into T-SQL on SQL Server 2016.

This is the way it is written in Teradata:

REGEXP_SUBSTR(cn.CONTRACT_PD_AOR,'\b([a-zA-Z]{2})-([[:digit:]]{2})-([[:digit:]]{3})(-([a-zA-Z]{2}))?\b')

The numbers in the curly brackets specify the number of characters that can match the specific REGEXP. So, this is looking for a contract number that look like this format: XX-99-999-XX

Is this not possible in T-SQL? Specifying the amount of characters to look at? So I would have to write something like this:

where CONTRACT_PD_AOR like '[a-zA-Z][a-zA-Z]-[0-9][0-9]-[0-9][0-9][0-9]-[a-zA-Z][a-zA-Z]%'

Is there not a simpler way to go about it?

Answer 1

While not an answer, with this method it makes things a little less panful. This is a way to set a format and reuse it if you'll need it multiple times in your code while keeping it clean and readable.

Set a format variable at the top, then do the needed replaces to build it. Then use the format name in the code. Saves a little typing, makes your code less fugly, and has the benefit of making that format variable reusable should you need it in multiple queries without all that typing.

Declare @fmt_CONTRACT_PD_AOR nvarchar(max) = 'XX-99-999-XX';
Set @fmt_CONTRACT_PD_AOR = REPLACE(@fmt_CONTRACT_PD_AOR, '9', '[0-9]');
Set @fmt_CONTRACT_PD_AOR = REPLACE(@fmt_CONTRACT_PD_AOR, 'X', '[a-zA-Z]');

with tbl(str) as (
select 'AA-23-234-ZZ' union all
select 'db-32-123-dd' union all
select 'ab-123-88-kk'
)
select str from tbl
where str like @fmt_CONTRACT_PD_AOR;