Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Regular expression and performance benefits

Sep 14 2010 6:47AM by Madhivanan   

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

Tags: regular_expression, tsql, BRH, SQL Server, #TSQL, #SQLServer,

2 · 40% · 13039



  • Hi Madhivan, Nice post. just a question... What about the drawback of LIKE in where clause when used in the 2nd case?

    commented on Sep 15 2010 12:19AM
    Sudeep Raj
    12 · 13% · 4306
  • Sudeep Raj,

    As long as % is not used at the begging it is fine

    commented on Sep 20 2010 9:24AM
    2 · 40% · 13039

Your Comment

Sign Up or Login to post a comment.

"Regular expression and performance benefits" rated 5 out of 5 by 1 readers
Regular expression and performance benefits , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]