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?

Regex is not supported for integer still you can user regex to search a text in SQL — Saddam Abu Ghaida
– Saddam Abu Ghaida, Commented Nov 18, 2012 at 10:50

Martin Smith · Accepted Answer · 2012-11-18 10:46:40Z

6

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'

answered Nov 18, 2012 at 10:46

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Itay.B Over a year ago

using the "or field1 like" few times will hurt the performance?

Martin Smith Over a year ago

@Itay.B - It will process the string multiple times so won't be as efficient as a RegEx. Installing a CLR assembly is quite easy though.

Martin Smith Over a year ago

@RoyiNamir - No but that's the same as just having a LIKE without a leading and trailing wildcard isn't it?

Itay.B Over a year ago

If i want to use a clr assembly i need the dba to approve it, which in my company is not an easy task.

Collectives™ on Stack Overflow

sql server regex

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related