sql server regex

Question

In SQL Server (2012) I have a field with numeric values. The field is of type `int`. Let's say that the field in the db contains `123456789`. I'm searching like this: ``` select * from table1 where field1 like '[1-9]{0,2}3456789' ``` I have also tried `'^[1-9]{0,2}$3456789'` Both are not working. What am I doing wrong?

Accepted Answer

SQL Server does not natively support Regular Expressions. You would need to install a CLR assembly for this.

The `LIKE` pattern syntax does not support quantifiers so without using CLR the only alternative is to expand it out.

```
where field1 like '3456789' 
 or  field1 like '[1-9]3456789'  
 or  field1 like '[1-9][1-9]3456789' 
```

sql server regex

Solution

Problem