linux n00b

Regex to create list of values for SQL statement

Posted in Howto, SQL by downforce on March 19, 2012

In my day I’m currently undertaking a lot of data migrations. One thing I have to do frequently is find a list of records that do and/or don’t exist in one or more tables. This usually involves getting a list of ID’s from a log output text files then doing SQL queries to attempt to find them!

Unfortunately our clients machines are generally Windows based. Thankfully most are kind enough to allow us to use/install Notepad++ (IMO  the best notepad software available on Windows).

As such I utilise Regex a lot to find/strip/edit the contents to get the ID’s out and today I had a brainwave I thought I’d share on how to get it out quickly and turn it into a list that you can cut and paste into a WHERE clause of a SQL statement.

In our case the format of the logs are:

<ID><Tab character><Error message in text>

What I want is:

'<ID>',

And I get there with:

s/^(.*)/'\1',/

Or in Notepad++:

Enjoy!

Advertisements
Tagged with: