Macro to format SQL strings for use in code

Macro to format SQL strings for use in code

1
NewbieNewbie
1

    Apr 02, 2006#1

    I'd like to create a macro that would edit a text file 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')
    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.

    6,683583
    Grand MasterGrand Master
    6,683583

      Apr 02, 2006#2

      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
      
      Best regards from an UC/UE/UES for Windows user from Austria