Macro to format SQL strings for use in code

Jaymac3 · Apr 02, 2006#12006-04-02T03:48+00:00

I'd like to create a macro that would edit a text file like:

SELECT     C.CompanyName, O.OrderDate, O.OrderID, OD.UnitPrice, OD.Quantity
FROM         Customers AS C INNER JOIN
                     Orders AS O ON C.CustomerID = O.CustomerID INNER JOIN
                     [Order Details] AS OD ON O.OrderID = OD.OrderID
WHERE     (C.CompanyName = N'Smith')

into a string like:

Code: Select all

"SELECT     C.CompanyName, O.OrderDate, O.OrderID, OD.UnitPrice, OD.Quantity " +
"FROM         Customers AS C INNER JOIN " +
"                      Orders AS O ON C.CustomerID = O.CustomerID INNER JOIN " +
"                      [Order Details] AS OD ON O.OrderID = OD.OrderID " +
"WHERE     (C.CompanyName = N'Smith') ";

I've used UE for years to insert the left-hand ", trim the trailing spaces, and paste the " + to the end of the line. But I'm pretty sure it should be able to create a macro to do this.

I've tried recording, but the process gets all bollixed up. If someone could post a start, I'd appreciate it.

Hint: one of my key problems is finding the end of a line to paste the " + to, and then moving to the next line.

Any suggestions are appreciated. This is a great tool, I've used it for years (since 3.5 or so) and I wouldn't be without it.

Thanks.

Mofi · Apr 02, 2006#22006-04-02T20:36+00:00

This text reformatting on entire file can be done with following macro code written for UltraEdit for Windows ≥ v10.20 and < v25.00 and UEStudio < v18.00.

Code: Select all

InsertMode
ColumnModeOff
HexOff
UnixReOff
TrimTrailingSpaces
Bottom
IfColNumGt 1
InsertLine
IfColNumGt 1
DeleteToStartofLine
EndIf
EndIf
Top
Find MatchCase RegExp "%^(*^)$"
Replace All ""^1 " +"
Bottom
Find MatchCase Up " +"
Replace ";"
Top

The macro first deletes all trailing spaces/tabs. Then it moves caret to end of file. If the caret is positioned at a column greater than 1, the last line of the file has no line termination. In this case one more line is inserted at end of file to insert a line ending at bottom of the file. This can result in automatic insert of leading spaces/tabs if auto-indent feature of UE/UES is enabled and last line in file has leading spaces/tabs. For that reason one more check is made if the caret is again at a column greater than 1 which means leading spaces/tabs were inserted by UE/UES in addition to the newline character(s) which need to be deleted. Next a simple tagged regular expression replace is executed to insert a " at beginning of each line and " + at end of each line. Finally the caret is moved back once again to bottom and a single replace is executed searching upwards for a space and plus sign being replace by a semicolon.

The same macro code for usage with UltraEdit for Windows ≥ v25.00 and UEStudio ≥ v18.00:

Code: Select all

InsertMode
ColumnModeOff
HexOff
UltraEditReOn
TrimTrailingSpaces
Bottom
IfColNumGt 1
InsertLine
IfColNumGt 1
DeleteToStartofLine
EndIf
EndIf
Top
Find MatchCase RegExp "%^(*^)$"
Replace All "\"^1 \" +"
Bottom
Find MatchCase Up " +"
Replace ";"
Top