Regular expressions again

Right. Now I understand regular expressions a little better here's an update to the previous post. Rather than use the rather clunky .NET string manipulation of the previous post this is a little neater:

Dim dateRegEx As New RegularExpressions.Regex("\d{4}[-]\d{2}[-]\d{2}[-]\d{2}.\d{2}.\d{2}.\d{6}")

If dateRegEx.IsMatch(StringToFix) Then

outString = dateRegEx.Replace(StringToFix, _
"(?\d{4})-(?\d{2})-(?\d{2})-(?\d{2}).(?\d{2}).(?\d{2}).(?\d{3})(?\d{3})", _
"${year}-${month}-${day} ${hour}:${min}:${sec}.${milli}")

End If

This does everything the previous code fragment does but with less than half the code. The regular expressions work as follows:

\d - in this example has to be used with a repetition count (the number in the {}-brackets). Essentially this matches the number of digits in the curly brackets. This was probably where I was going wrong in the previous post. So that tidies up the match.

The Replace function has quite a large number of overloads but the one being used is here.

the ?<year> notation in the second replace parameter identifies a regular expression group (in this case 'year'), essentially splitting up the string being evaluated, for manipluation in the third parameter.

The ${} notation is explained here, and in this example I'm just using it to concatenate the various bits of string groups created by using ?<group name> notation together.

Regular expressions are your friend

Well apparently there are people reading this - 23 so far if Google Analytics is to be believed. Anyway on with the post...

We're importing some frankly bizarre DB2 flat files using SSIS, one of the features of which is a date format yyyy-mm-dd-hh.mm.ss.mmmmm which SQL obviously won't accept as a datetime format (they're also fixed width and delimited - work that one out). For this and other reasons the easiest thng to do is to parse the file in a Script Task. The RegularExpressions .NET class is dead handy for this (Useful resource here ).

Add a script task do the necessary getting of the file using System.IO class and split the line using the Split method (note that the seperator is a Char data type not string), we need to reference the right RegularExpression class:

Imports System.Text.RegularExpressions

Then create an immutable regular expression:

Dim dateRegEx As New Regex("[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][-][0-9][0-9][.][0-9][0-9][.][0-9][0-9][.][0-9][0-9][0-9][0-9][0-9][0-9]")

Then match the string you want to fix with the regular expression and do your reformatting work:

If dateRegEx.IsMatch(StringToFix) Then
revString = StrReverse(StringToFix)
outString = Replace(Replace(revString, "-", " ", , 1), ".", ":")
outString = StrReverse(Replace(outString, ":", ".", , 1)).Substring(0, 23)
End If

Not the cleverest regular expression for date-matching but sufficient for our purposes. Incidentally I found that the SSIS version of .NET didn't seem to like using the /d notation instead of [0-9]. Anyway doing this without RegEx would be a real pain.

There is actually a regular expression task at www.sqlis.com but we wanted to parse the file prior to loading into SSIS. Script Task and RegEx to the rescue...