evBlog

Every once in a while I'll load a temporary table up with data before I load it into a production table so I can do some manipulation of the data if needed. Today I was in the process of loading data into the production table when I noticed there were some duplicate entries in the data I was loading.  In fact there were many duplicates.  I did some searching and I found this code, it removes the duplicate records, leaving just one entry.

The code code was found at SQL Server Magazine's web site at the address below.

http://www.sqlmag.com/Article/ArticleID/21704/sql_server_21704.html

Hope this helps, I know I'll use it again.

Good Day,
Ryan

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

A post on <cfsilence> today got me think of joins and the right vs wrong way to do things.

When doing an SQL inner join is it best to have something like

SELECT    a.someThing1,a.someThing2
              b.someThing1,b.someThing2
FROM      someTable1 a INNER JOIN       
              someTable2 b ON a.someKey1 = b.someKey1       

or something like this

SELECT    a.someThing1,a.someThing2
              b.someThing1,b.someThing2
FROM      someTable1 a,
              someTable2 b
WHERE    a.someKey1 = b.someKey1

Notice the join is in the from statment in first example vs the where statement in the second example.   Is there a difference betweent the two?  Is one better than the other?  How do you do it?  How do you roll?

Good Day!
Ryan