Just Learned - TSQL - Use Regular expression for better performance

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


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

TSQL - Use Regular expression for better performance

Jul 5 2011 9:04PM by Madhivanan

Many people use functions over the column to search for certain pattern. For example you want to get data where column value starts with character A followed by a number and followed by character F. Usual method is

WHERE left(col,1)='A' and substring(col,2,1) between '0' and '9' and substring(col,3,1)='F'

The above condition wont make use of index if available on col. The effecient method is to use regular expression as follows which will make use of available index on col

WHERE col like '[A][0-9]F%'

You may also need to read this post too http://beyondrelational.com/blogs/madhivanan/archive/2010/09/14/regular-expression-and-performance-benefits.aspx

Read More.. [30 clicks]

Published under: SQL Server Tips · · · ·

Previous: Visual Studio 2010 Code Snippets helps us to type code better and faster
Next: SQL Server - Making your query to wait for some time by using WaitFor

Madhivanan

3 · 39% · 12472

Liked

Knew

Learned

Incorrect

Interesting

Forgotten


Post this to my facebook wall
Tweet this
Like This
I knew this before
Thanks, I just learned it from you
Sorry, but this is not correct
Interesting, but not my area
I knew it but i have forgotten it


	Submit

4 Comments

Nice one. Thanks for sharing

commented on Jul 5 2011 2:47AM

Hardik Doshi

20 · 9% · 2839

Thank-you for sharing this one, Madhivanan! I think this is a very common trap into which most developers fall into.

commented on Jul 5 2011 11:51PM

Nakul Vachhrajani

4 · 33% · 10680

Agreed with Nakul. Another worst practice I often see is that some people have the tendency to write like clauses as Status LIKE '%Active%' even when a Status LIKE 'Active%' is sufficient. It is important to remember that the second one can use an index, where as the first one cannot.

commented on Jul 6 2011 12:26AM

Jacob Sebastian

1 · 100% · 32004

Thanks....great stuff

commented on Jul 6 2011 9:05AM

Jason Yousef

152 · 1% · 319

Your Comment

Sign Up or Login to post a comment.

"TSQL - Use Regular expression for better performance" rated 5 out of 5 by 11 readers +Madhivanan