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:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: