Thursday, December 11, 2008

Help me make this Regular Expression better...

I was recently wanted to update a SQL Script file that was populating some seed (test) data into a bunch of database tables. I had scripted out the data in the table using a tool and all of the datetime columns were being updated with string values similar to the one below.

'20081208 13:54:31.236'



I wanted to update all of these to midnight of the current date, so that the data would be current every time the seed data is loaded. To do this I wrote the following T-SQL to put my date into this format in a variable:



DECLARE @CurrentDateString varchar(30)
SET @CurrentDateString = REPLACE(CONVERT(varchar(10), GETDATE(), 102),'.','') + ' 00:00:00'
PRINT 'Current Date: ' + @CurrentDateString




Now,  I wanted to update all of the existing string datetime values with my new variable. I had about 75 strings like this in the seed data script file. I fired up Notepad++ and started to work on a Regular Expression to find and the replace all of these entries. Since, writing Regular Expressions is not a common thing for me, after a little googling and some hacking, I came up with the following:



('20081208)\s[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'




This is probably not the most efficient expression that I could have written, but it go the job done. Anyone have any suggestions for making this better? Thanks!

No comments: