Define Range in SQL Query using Regular Expression

Question

I have the following values

ABCD_AB_1234
ABCD_ABC_2345
ABCD_ABCD_5678

and a regular expression to match them

ABCD_[A-Z]{2-4}_[0-9]{4}

Now I am looking to convert that regular expression to a SQL query so I can get those records back from the database.

Right now I have following where clause

where [columnName] like 'ABCD_[A-Z][A-Z]%[_][0-9][0-9][0-9][0-9]%'

The problem is that I cannot define a range in the SQL query as I did in the regular expression, like {2-4}, what I am doing now is to set the minimum range only.

Is there any solution?

Don't confuse pattern matching for full regex, SQL Server does not (natively) support it. — Stu, Dec 01 '22 at 22:18

score 1 · Answer 1 · answered Dec 01 '22 at 22:19

Assuming you are explaining the full picture the easiest way is probably to create 3 conditions to cover your scenarios e.g.

where [columnName] like 'ABCD_[A-Z][A-Z][_][0-9][0-9][0-9][0-9]%'
   or [columnName] like 'ABCD_[A-Z][A-Z][A-Z][_][0-9][0-9][0-9][0-9]%'
   or [columnName] like 'ABCD_[A-Z][A-Z][A-Z][A-Z][_][0-9][0-9][0-9][0-9]%'

Its not optimal but SQL Server doesn't have any regex support so if you have to do it in SQL this is one way.

Define Range in SQL Query using Regular Expression

1 Answers1