Today I was pulling in some SQL code from SQL Studio I wanted to put into a string and run from a VBA routine to update some views on the production database.

Because I use MZ Tools, I chose to use the find and replace function along with regular expressions to pick up new lines.

Basically, I pasted the whole string with line breaks and all from SQL Studio. (I had first opened it in a new query window in SQL Studio so I could select just the SQL statement that I wanted, which was a CREATE OR ALTER ‘view_name” AS SELECT blah blah blah.

Once I had the text in my VBA editor (on it’s own set of lines with a blank line above and below), I selected the entire block and hit Ctrl-F on my keyboard (which I set up as the MZ Tools hotkey). Then I selected the Regular Expressions option checkbox and the find in Selected Text option. Finally, as my search criteria I wanted to find all line breaks, which in regular expressions in MZ Tools is represented by \r\n.

Ok, so now it selected all my line breaks and put them in the results window. At this point I could replace the line breaks.

I wanted there to be a space, then a double quote, then an ampersand and an underscore to continue the line. After the line break I put 8 spaces (two standard tabls) and another quote to start off the next line.

Replacement string between the start and end tags: <start> ” & _\r\n “<end>

Then I said replace all nodes and away it went. Basically, the only thing this didn’t do for me was add the tabs and quote to the beginning of the first line and it didn’t add a quote to the very end of the last line.

One of my queries was super long so when I highlighted the whole thing it couldn’t finish because there were too many line breaks. MZ Tools gracefully stopped and I was able to just continue it on again by doing the same thing with the portion of the string it hadn’t been able to convert and I ended up having a variable to concatenate them together.