Skip to main content

Use Regular Expressions to Search and Replace in Coda or TextMate

Warning: This post is over a year old. I don't always update old posts with new information, so some of this information may be out of date.

How to perform searches using regular expressions.

As in the original post, I'd generated a list of node ID values, and needed to add structure the SQL update statment formatted in a certain way. However, I changed my inital query slightly to out put the same nid value twice.

SELECT nid, nid FROM node WHERE TYPE = 'blog' ORDER BY nid ASC;

Then, I could select all of the returned rows, copy the values, and paste them into Coda:

As before, I needed my SQL update statement to be in the following format:

INSERT INTO term_node VALUE (nid, vid, tid), (nid2, vid2, tid);

As I mentioned previously, the nid and vid values are the same for each node, and the tid will remain constant. In this case, the tid value that I needed to use was '63'.

So, using the 'Find and Replace' function within Coda, combined with regular expressions (regex), I can easily format the values as needed. To begin with, I need to ensure that the RegEx search option is enabled, and that I'm using the correct escape character.

The first thing that I wanted to do was add the seperating comma between the two values. To do this, I perform a search for \s*\t. This searches for everything that is whitespace AND is a tab value. I can then add the comma as the replacement for each result.

All 31 lines have been changed.

Next, I can use \n to target the lines between the rows. I'll replace it with the next comma, the number 63 (the tid value), the closing bracket, another comma, re-add the line and add the opening bracket.

The only two lines that aren't changed are the first and last, as they don't have any line breaks following them. I can complete these lines manually. Now all I need to do is add the beginning of the SQL update statement, then copy and paste it into Sequel Pro.

Comments or questions? I'm @opdavies on Twitter.