evBlog

Good morning all, last week I was tasked with a new project at work.  This project entailed taking string of characters and matching them up with an appropriate systems.  The string of characters were account numbers and the systems were billing systems.  See here at the company I work for we have hundreds if not thousands of different looking account numbers.  Here are an example of just a few. 

0652849190293
517M394586
313E880350350
178668      
IIRSLUXW    
NV300083    
205SWTA280100A2800000
1788555400001
AL0000000000011999716

Now the reason we have so many different type of account numbers is beside the point.  Things like this just happen when you work in an industry that is ripe for spin-offs and consolidations.  However what this post is about is Regular Expressions!!!  Not just regular expressions in CF, but regular Expressions on MS SQL server.  A quick Google search for “MS Regular Expressions” led me to “The Code Project” site and to this article and download.  In the article is a zip file containing four extended stored procedures to be loaded in the master DB of your MS SQL server.  These four procedures; xp_regex_match, xp_regex_format, xp_regex_split and xp_regex_replace are very easy to load and use.  In no time I had downloaded the file and created the procedures on my server for use. 

So with these procedures in place I wrote a simple form in ColdFusion that allows a user to input a single value for an account number.  ColdFusion then calls the procedure below which loops over a series of Regular Expression statements stored in a table.  If the account number matched that result is saved in a list then the details are sent back to the user.

Pretty cool stuff if you ask me.  And there really is no better way to learn more about Regular Expressions than to have to write 177 different expressions to match possible account numbers.   And I've only begun to scratch the surface.

Good Day!
Ryan