Regular expression can be effectively used to get performance benefit in certain cases. Consider the following customers table where custid is the primary key.

Source Data

declare @customers table
(
custid varchar(10) primary key, 
custname varchar(100),
zipcode char(6)
)
insert into @customers(custid,custname,zipcode)
select 'CUS001','customer1','z00001' union all
select 'CUS002','customer2','z00001' union all
select 'CUS611','customer3','z00002' union all
select 'CUS021','customer4','z00003' union all
select 'CUS871','customer5','z00003' union all
select 'CUS088','customer6','z00001' union all
select 'CUS701','customer7','z00002'

Suppose you want to query it in such a way that first three characters should be CUS and the fourth to six chacters should be between 000 and 099. A common approach is to use sunstring function

select * from @customers
where substring(custid,1,3)='CUS' and 
substring(custid,4,3) between '000' and '099'

Becuase substring function is used and custid has a clustered index, it cannot be used thus causing a table scan

But a regular expression can be used effectively to make use of index which causes index seek as shown below

select * from @customers
where custid like 'CUS[0][0-9][0-9]%'

In both the cases the results are

custid     custname    zipcode
---------- ----------  --------
CUS001     customer1   z00001
CUS002     customer2   z00001
CUS021     customer4   z00003
CUS088     customer6   z00001

The following post uses regular expressions for other purposes
http://beyondrelational.com/blogs/madhivanan/archive/2009/08/03/regular-expression.aspx http://beyondrelational.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx