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?

Answer 1

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'