Imagine this example...
Code: Select all
SELECT DATE() AS Today_Date ,NOW() AS Today_Now ,CDate(DateUpdate) AS DateUpdate_CDate ,CCur([
flags]) AS Flags_CCur ,NZ([LV], "LV NULL") AS LV_NZ ,CInt([Type]) AS Type_CInt ,TRIM(Space(20) &
NAME & " ") AS Name_Trim ,LEFT(NAME, 4) AS Name_Left4 ,RIGHT(NAME, 4) AS Name_Right4 ,MID(NAME,
4,5) AS Name_Mid45 ,NOT INSTR(1, NAME, "q") <> 0 AS Name_Contains_q ,IIF(Type = 5, "Query",
"Other") AS Type_IIF ,IIF(ISNULL(LvProp), "N/A", LvProp) AS LvProp_Handler ,"10 + 10 = " & CStr(
10 * 2) AS IgnoreMathSymbols INTO NewTable FROM SampleTable WHERE [NAME] LIKE "*e*" AND Right(
DATE(), 4) = 1010 * 2 + 1;
Code: Select all
SELECT
DATE() AS Today_Date
,NOW() AS Today_Now
,CDate(DateUpdate) AS DateUpdate_CDate
,CCur([flags]) AS Flags_CCur
,NZ([LV], "LV NULL") AS LV_NZ
,CInt([Type]) AS Type_CInt
,TRIM(Space(20) & NAME & " ") AS Name_Trim
,LEFT(NAME, 4) AS Name_Left4
,RIGHT(NAME, 4) AS Name_Right4
,MID(NAME, 4,5) AS Name_Mid45
,NOT INSTR(1, NAME, "q") <> 0 AS Name_Contains_q
,IIF(Type = 5, "Query", "Other") AS Type_IIF
,IIF(ISNULL(LvProp), "N/A", LvProp) AS LvProp_Handler
,"10 + 10 = " & CStr(10 * 2) AS IgnoreMathSymbols
INTO NewTable
FROM SampleTable
WHERE
[NAME] LIKE "*e*"
AND Right(DATE(), 4) = 1010 * 2 + 1;
I've been using this for a few years and I'm glad to give something back to this community.
I am primarily using Access SQL and SQL Server T-SQL. It may not be perfect for some other SQL language variations.
Code: Select all
;-----------------------------------------------------------------------------
; SQL Pretty Formatter
;
; A script by Talada was the inspiration for me to develop something much more comprehensive.
; https://autohotkey.com/board/topic/42341-sql-pretty-formatter/
;
; Developed by Ben on 7/6/2020 and shared here:
; https://www.autohotkey.com/boards/viewtopic.php?f=6&t=107188
;
; Not so great with commented SQL, but if it has comments it may already be formatted.
; A nested SELECT inside parenthesis may not get <CR> after a comma.
; CTE queries won't be formatted very well because of parenthesis.
;
; BS 2/14/2021: Added some support for formatting SQL Server CTE queries.
; BS 5/3/2022: Added support for inline comments
; BS 6/7/2022: Added support for the TOP modifier of the SELECT statement so it stays on the same line.
; BS/CK 9/27/2022: Optimized conversions related to finding commas that aren't in
; between parenthesis, quotes, brackets, and braces.
; Created function RegExReplaceExcludeNoWordBoundary() to do a more consistent job.
; About 6 hours (x2) of pair-programming went into this version.
;-----------------------------------------------------------------------------
#SingleInstance Force
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
Menu, Tray, Icon, shell32.dll, 85 ; Set the tray icon of folder hierarchy.
$^1:: ; Ctrl+1
;^!1:: ; Ctrl+Alt+1 for use in remote desktop
IfWinActive ahk_exe CDViewer.exe
{
SoundBeep 420, 150
Send, ^1
return
}
else IfWinActive ahk_class XLMAIN ; Let the key work normally in Excel so CTRL+1 can show the Format Cells dialog.
{
SoundBeep 420, 150
Send, ^1
return
}
else
{
#1:: ; Win+1
;^+R:: ; CTRL+Shift+R for use in remote desktop
if IsTextSelected() {
;s := gst()
;if (s <> "") {
ToolTip Formatting selected SQL with AutoHotkey...
Autotrim, Off
; ; MsgBox, 64, 2 second timeout, Clipboard = %ClipBoard%, 2 ; MsgBox with timeout
; ; return
ClipSaved:=ClipboardAll ; Preserve the Clipboard
Clipboard := "" ; Empty the Clipboard
Sleep, 50 ; Added to prevent Windows going B&W (Win+CTRL+C)
; Send ^c
SendInput ^c
ClipWait, 2
if ErrorLevel
{
MsgBox, 64, Timeout in 4 seconds..., The attempt to copy text onto the clipboard failed., 4
ToolTip
return
}
; Tab characters are not supported in the Access SQL editor so use spaces instead.
IfWinActive , ahk_class OMain ; Access
{
Tab := " "
; If I don't show a MsgBox when pulling from Access, the variable is empty.
Sleep, 500
} else {
Tab = %A_Tab%
}
;Tab := "^^^^"
s := Clipboard
s := SQL_Pretty_Format(s, Tab, "") ; Call the function that does all the work.
;MsgBox, 64, 2 second timeout, Clipboard = %s%, 4 ; MsgBox with timeout
; MsgBox, 32, 1 second timeout, S = %s%,1
; MsgBox, 64, 4 second timeout, %s%, 4 ; MsgBox with timeout
;sleep 20
Clipboard := s
ClipWait, 1
SendInput ^v
SoundBeep 420, 150
Clipboard := ClipSaved ; Restore the Clipboard
ClipSaved := "" ; Free the memory in case the Clipboard was very large.
ToolTip
}else{
MsgBox, 64, 4 second timeout, The SQL formatter only works if you have selected text., 4 ; MsgBox with timeout
}
return
; Format selected text for VBA
^!+1:: ; Ctrl+Alt+Shift+1 for use in remote desktop
#+1:: ; Win+Shift+1
if IsTextSelected() {
ToolTip Formatting selected SQL with AutoHotkey for VBA...
SoundBeep 420, 150
Autotrim, Off
ClipSaved:=ClipboardAll ; Preserve the Clipboard
; MsgBox, 64, 2 second timeout, Clipboard = %ClipBoard%, 2 ; MsgBox with timeout
; return
Clipboard := "" ; Empty the Clipboard
Sleep, 50 ; Added to prevent Windows going B&W (Win+CTRL+C)
Send ^c
;SendInput ^c
ClipWait, 2
if ErrorLevel
{
; Try a second time
Send ^c
;SendInput ^c
ClipWait, 1
if ErrorLevel
{
MsgBox, The attempt to copy text onto the clipboard failed.
ToolTip
return
}
}
; Tab characters are not supported in the Access SQL editor so use spaces instead.
IfWinActive , ahk_class OMain ; Access
{
Tab := " "
; If I don't show a MsgBox when pulling from Access, the variable is empty.
Sleep, 100
} else {
Tab = %A_Tab%
}
;Tab := "^^^^"
s := Clipboard
s := SQL_Pretty_Format(s, Tab, "VBA")
; InputBox, s, After SQL Pretty Format, %s%,,900, 500,,,,5, %s%
s := StrReplace(s, "`r`n", "`n" )
s := StrReplace(s, "`n`t", "`n" ) ; Remove tabs
s := wrap(s, 120, "~~", false) ; Apply word wrap to long lines
; InputBox, OutputVar, After Wrap, %s%,,900, 500,,,,5, %s%
; After word wrap, convert it to VBA.
s := StrReplace(s, """", """""" ) ; Fix quotes
s := StrReplace(s, "`n", """ & vbCrLf _`r`n`t& """)
s := StrReplace( s, "`r`n`r`n", "`r`n" ) ; Remove double CrLf
s := "strSQL = """ . s . """"
;InputBox, OutputVar, VBA formatted query, %s%,,900, 500,,,,5, %s%
; Only 25 line continuations are allowed in VBA. Find them and replace with "strSQL = strSQL & ..."
; Find: ^(.*?(vbCrLf _.*?){20})_\n\t need to use options "ms"
; Replace: \1vbCrLf\r\nstrSQL = strSQL
; https://regex101.com/r/wP7pR2/78
; It took me about 6 hours to figure out this RegEx. BS 2/15/2021
s := RegExReplace( s, "ms)^(.*?(vbCrLf _.*?){23})_`r`n`t", "$1`r`nstrSQL = strSQL " )
;InputBox, OutputVar, VBA formatted query, %s%,,900, 500,,,,5, %s%
Clipboard := s
ClipWait, 2
SendInput ^v
SoundBeep 420, 150
Clipboard := ClipSaved ; Restore the Clipboard
ClipSaved := "" ; Free the memory in case the Clipboard was very large.
ToolTip
}else{
MsgBox, 64, 4 second timeout, The SQL formatter only works if you have selected text., 4 ; MsgBox with timeout
; One example of this happening is selecting in an Outlook email that is being composed. BS: 9/12/2020
}
return
ExitApp
;---------------------------------------------------------
SQL_Pretty_Format(s, Tab = "%A_Tab%", Mode = "")
{
; Decide if we want to put a carriage return after each major keyword
if (Mode="VBA")
CrAfterKeyword:=" " ; %A_Space%
else
CrAfterKeyword:="`r`n" . Tab
s := " " . s . " "
;--- Handle Inline Comments -------------------------------------------------------------
if (Mode="VBA")
s := RegExReplace( s, "(--.*)", "") ; Remove inline comments for VBA. BS/CK 9/27/2022
else {
s := RegExReplace( s, "(--.*)", "{{$1}}") ; Find T-SQL inline comments and wrap them in double braces to be swapped at the end. BS 5/3/2022
; BS/CK 9/27/2022: We had some issues with RegEx working with the chevrons so we changed "«$1»" to "{{$1}}"
s := RegExReplace( s, "[`r`n]+(\s*?)({{)", "$2°$1") ; Find inline comments on their own line with or without leading whitespace and preserve them. BS/CK 7/8/2022
} ;--------------------------------------------------------------------------------------
s := StrReplace( s, "`r`n", " " )
s := StrReplace( s, A_Tab, " " )
s := RegExReplace( s, "\s+", " ") ; Replace multiple spaces with a single space.
s := RegExReplace( s, "\(\s+", "(") ; Remove spaces following an open parentheses.
s := RegExReplace( s, "\s+\)", ")") ; Remove spaces before a close parentheses.
;s := StrReplace( s, "( (", "((" )
;s := StrReplace( s, ") )", "))" )
; s := StrReplace( s, ", ", "," )
; s := StrReplace( s, " ,", "," )
s := RegExReplaceExcludeNoWordBoundary( s, ",\s", "," )
s := RegExReplaceExcludeNoWordBoundary( s, "\s,", "," )
;MsgBox, 64, 4 second timeout, %s%, 44 ; MsgBox with timeout
; Start with UNION because a SELECT always follows a UNION.
s := StrReplace( s, " union all", "`r`n`r`nUNION ALL" . CrAfterKeyword . " " )
s := StrReplace( s, " union ", "`r`n`r`nUNION" . CrAfterKeyword . " " )
s := StrReplace( s, " select *", "`r`nSELECT *")
s := StrReplace( s, " select distinct ", "`r`nSELECT DISTINCT" . CrAfterKeyword)
; BS 6/7/2022: Added support for the TOP modifier of the SELECT statement so it stays on the same line.
; Start the search with "i)" to turn on the case-insensitive option.
s := RegExReplace( s, "i)(\bSELECT TOP [\(\)\d]* (WITH TIES|PERCENT))\s", "`r`n$1" . CrAfterKeyword) ; Example: SELECT TOP 124433253 WITH TIES FirstName
s := RegExReplace( s, "i)(\bSELECT TOP [\(\)\d]*)\s(?!.*WITH\s)(?!.*PERCENT\s)", "`r`n$1" . CrAfterKeyword) ; Example: SELECT TOP 124433253 FirstName
s := StrReplace( s, " select ", "`r`nSELECT" . CrAfterKeyword) ; Carriage return added before SELECT
s := StrReplace( s, " insert into ", "`r`nINSERT INTO" . CrAfterKeyword )
s := StrReplace( s, " insert ", "`r`nINSERT" . CrAfterKeyword )
s := StrReplace( s, " values ", "`r`nVALUES" . CrAfterKeyword )
s := StrReplace( s, " into ", "`r`nINTO " . CrAfterKeyword ) ; Ben prefers no CR
s := StrReplace( s, " update ", "`r`nUPDATE" . CrAfterKeyword )
s := StrReplace( s, " set ", "`r`nSET" . CrAfterKeyword )
s := StrReplace( s, " delete from ", "`r`nDELETE FROM" . CrAfterKeyword )
s := StrReplace( s, " delete ", "`r`nDELETE" . CrAfterKeyword )
if (Mode="VBA")
{
; s := StrReplace( s, " from ", "`r`nFROM " )
s := RegExReplaceExclude( s, "from ", "`r`nFROM" )
} else {
;--- Produce a more condensed FROM clause ---
s := RegExReplaceExcludeNoWordBoundary( s, "from ", "`r`nFROM" . CrAfterKeyword ) ; Ben prefers no CR
s := RegExReplaceExclude( s, "full outer join", "`r`n" . Tab . "FULL OUTER JOIN" )
s := RegExReplaceExclude( s, "left outer join", "`r`n" . Tab . "LEFT OUTER JOIN" )
s := RegExReplaceExclude( s, "right outer join", "`r`n" . Tab . "RIGHT OUTER JOIN" )
s := RegExReplaceExclude( s, "left join", "`r`n" . Tab . "LEFT JOIN" )
s := RegExReplaceExclude( s, "right join", "`r`n" . Tab . "RIGHT JOIN" )
s := RegExReplaceExclude( s, "inner join", "`r`n" . Tab . "INNER JOIN" )
s := RegExReplaceExclude( s, "cross join", "`r`n" . Tab . "CROSS JOIN" )
; s := StrReplace( s, " join ", "`r`nJOIN`r`n" . Tab )
s := RegExReplaceExclude( s, "create or alter", "`r`nCREATE OR ALTER" )
s := RegExReplaceExclude( s, "create", "`r`nCREATE" )
s := RegExReplaceExclude( s, "alter", "`r`nALTER" )
s := RegExReplaceExclude( s, "table", "TABLE")
s := RegExReplaceExclude( s, "column", "COLUMN")
;--- Enable these if you prefer the FROM clause on more lines ---
; s := StrReplace( s, " from ", "`r`nFROM`r`n" . Tab )
; s := StrReplace( s, " full outer join ", "`r`nFULL OUTER JOIN`r`n" . Tab )
; s := StrReplace( s, " left outer join ", "`r`nLEFT OUTER JOIN`r`n" . Tab )
; s := StrReplace( s, " right outer join ", "`r`nRIGHT OUTER JOIN`r`n" . Tab )
; s := StrReplace( s, " left join ", "`r`nLEFT JOIN`r`n" . Tab )
; s := StrReplace( s, " right join ", "`r`nRIGHT JOIN`r`n" . Tab )
; s := StrReplace( s, " inner join ", "`r`nINNER JOIN`r`n" . Tab )
; s := StrReplace( s, " cross join ", "`r`nCROSS JOIN`r`n" . Tab )
; s := StrReplace( s, " join ", "`r`nJOIN`r`n" . Tab )
}
s := RegExReplaceExcludeNoWordBoundary( s, "where ", "`r`nWHERE" . CrAfterKeyword )
s := RegExReplaceExclude( s, "group by", "`r`nGROUP BY" . CrAfterKeyword )
s := RegExReplaceExclude( s, "order by", "`r`nORDER BY" . CrAfterKeyword )
s := RegExReplaceExclude( s, "having", "`r`nHAVING" . CrAfterKeyword )
s := RegExReplaceExclude( s, "transform", "`r`nTRANSFORM" . CrAfterKeyword )
s := RegExReplaceExclude( s, "pivot", "`r`nPIVOT" . CrAfterKeyword )
s := RegExReplaceExclude( s, "except", "`r`nEXCEPT" . CrAfterKeyword )
s := RegExReplaceExclude( s, "and", "`r`n" . Tab . "AND")
s := RegExReplaceExclude( s, "or", "`r`n" . Tab . "OR")
s := RegExReplaceExclude( s, "is", "IS")
s := RegExReplaceExclude( s, "as", "AS")
s := RegExReplaceExclude( s, "not", "NOT")
s := RegExReplaceExclude( s, "null", "NULL")
s := RegExReplaceExclude( s, "like", "LIKE")
s := RegExReplaceExclude( s, "with", "WITH")
s := RegExReplaceExclude( s, "true", "TRUE")
s := RegExReplaceExclude( s, "false", "FALSE")
s := RegExReplaceExclude( s, "left", "LEFT")
s := RegExReplaceExclude( s, "right", "RIGHT")
s := RegExReplaceExclude( s, "isnull", "ISNULL")
s := RegExReplaceExclude( s, "coalesce", "COALESCE")
s := RegExReplaceExclude( s, "cast", "CAST")
s := RegExReplaceExclude( s, "convert", "CONVERT")
s := RegExReplaceExclude( s, "case", "CASE")
s := RegExReplaceExclude( s, "when", "WHEN")
s := RegExReplaceExclude( s, "else", "ELSE")
if (Mode<>"VBA") {
; Find AND and OR words inside IIF() statments and move them to a new line. Run this multiple times. BS 9/29/2022
; https://regex101.com/r/ZrvknK/1
s := RegExReplace( s, "ims)(IIF.*?\n\t)(AND|OR)(\b.*?\bAS\b)","$1`t$2$3")
s := RegExReplace( s, "ims)(IIF.*?\n\t)(AND|OR)(\b.*?\bAS\b)","$1`t$2$3")
s := RegExReplace( s, "ims)(IIF.*?\n\t)(AND|OR)(\b.*?\bAS\b)","$1`t$2$3")
s := RegExReplace( s, "ims)(IIF.*?\n\t)(AND|OR)(\b.*?\bAS\b)","$1`t$2$3")
}
s := RegExReplaceExclude( s, "constraint", "`r`n" . Tab . "CONSTRAINT" )
s := RegExReplaceExclude( s, "constraint", ",`r`n" . Tab . "CONSTRAINT" )
s := RegExReplaceExclude( s, "foreign key", "`r`n" . Tab . "FOREIGN KEY" )
s := RegExReplaceExclude( s, "begin transaction", "`r`nBEGIN TRANSACTION`r`n" )
s := RegExReplaceExclude( s, "begin", "`r`nBEGIN`r`n" )
s := RegExReplaceExclude( s, "end", "END" )
;s := RegExReplaceExclude( s, "end", "`r`nEND`r`n" ) ; While this works great with BEGIN, it is annoying with CASE WHEN. BS 10/7/2022
s := RegExReplaceExclude( s , "rollback", "`r`nROLLBACK`r`n" )
s := RegExReplaceExclude( s , "commit", "`r`nCOMMIT`r`n" )
s := RegExReplaceExclude( s , "go", "`r`nGO`r`n`r`n" )
; Return s
; MsgBox, 64, 4 second timeout, %s%, 44 ; MsgBox with timeout
;--- Find commas not in parenthesis and prefix with <CR><Tab> --------------------------------
; This Regex will first build a group of nested parenthesis, comments wrapped in {{double braces}},
; square brackets, and quotes. It will SKIP that group and then find any remaining commas
; and include a carriage return with them.
; https://regex101.com/r/ITb5gb/1
; Find Nested Parenthesis: https://stackoverflow.com/a/3851098/1898524
; The PCRE Regex using ?R does not work with (*SKIP)(*FAIL) where the ?1 method does. BS 9/29/2022
; I created an example here: https://regex101.com/r/xkVzVP/1
if (Mode="VBA")
;s := StrReplace( s, "¦,¦" , ",`r`n" )
;s := RegExReplace(s, "(\((?:[^()]++|(?R))*\)|({{[^{][^{]*}})|(\[.*?\])|\""[^\""]*\"")(*SKIP)(*FAIL)|,", ",`r`n")
s := RegExReplace(s, "(\((?>[^()]+|(?1))*\)|({{[^{][^{]*}})|(\[.*?\])|\""[^\""]*\"")(*SKIP)(*FAIL)|,", ",`r`n")
else
;s := RegExReplace(s, "(\((?:[^()]++|(?R))*\)|({{[^{][^{]*}})|(\[.*?\])|\""[^\""]*\"")(*SKIP)(*FAIL)|,", ",`r`n" . Tab ) ; Use this to suffix columns with a comma at the end of each line.
s := RegExReplace(s, "(\((?>[^()]+|(?1))*\)|({{[^{][^{]*}})|(\[.*?\])|\""[^\""]*\"")(*SKIP)(*FAIL)|,", "`r`n" . Tab . ",") ; or this to prefix commas at the beginning of each line.
; s := RegExReplace(s, "((\((?>[^()]+|(?1))*\))|(\{\{[^\{][^\{]*\}\})|(\[.*?\])|\""[^\""]*\"")(*SKIP)(*FAIL)|,", "`r`n" . Tab . ",") ; or this to prefix commas at the beginning of each line.
; MsgBox, 64, 4 second timeout, %s%, 44 ; MsgBox with timeout
; Find all remaining commas between words and put a space after the comma. BS 1/26/2021
;s := RegExReplace( s, "(\S)(,)(\S)", "$1$2 $3" )
s := RegExReplaceExcludeNoWordBoundary( s, "(\S)(,)(\S)", "$6$7 $8" )
; and handle the special case where [field1],[field2] was not getting a space added. BS/CK 9/27/2022
s := RegExReplace(s, "(({{[^{][^{]*}})|\""[^\""]*\"")(*SKIP)(*FAIL)|\],(\S)", "], $3")
;Find Commas that precede quoted strings and add a space between them. BS/CK 9/29/2022
;https://regex101.com/r/mI983B/1
s := RegExReplace(s, "(\S,)(\""[^\""]*\"")", "$1 $2")
;---------------------------------------------------------------------------------------------
; If there is an embedded SELECT start it on a new line.
s := StrReplace( s, "(select ", "(`r`nSELECT ")
; Special case for formatting SQL Server CTE queries. BS 2/14/2021
; https://regex101.com/r/HywKHi/1/
;s := RegExReplace( s, "(\t*,)(.*AS \()\s*(SELECT)", ",`r`n$2`r`n$3" ) ; Use this version to not add a CTE comment.
s := RegExReplace( s, "(\t*,)(?:(.*\/\*.*?\*\/)*)(.*)(.*AS \()\s*(SELECT)", ", /*== CTE ==*/`r`n$3$4`r`n$5" )
; Remove unnecessary parenthesis around single fields. BS 7/23/2021
; Example: WHERE ((table.field)=val(field2)) AND val((-1*(dog))/(cat))
; https://regex101.com/r/XPUlUv/4/
; Find: ([\s|\(])(\()([a-zA-Z0-9_.]*?)(\))
; Replace: \1\3
; On the SQL Converter website we implemented a "Regular Expression on Group 1"
; way to process RegEx based on http://www.rexegg.com/regex-best-trick.html#thetrick
; There is a (*SKIP)(*FAIL) syntax that will tell it to skip processing Group 0
; and allow us to do the same thing without additional programming! BS 10/11/2021
; \[[^]]*](*SKIP)(*FAIL)|([\s|\(])(\()([a-zA-Z0-9_.]*?)(\))
; BS/CK 9/27/2022: Added support for math characters +-*/
s := RegExReplace( s, "\[[^]]*](*SKIP)(*FAIL)|([\s|\(\+\-\*\/])(\()([a-zA-Z0-9_.]*?)(\))", "$1$3" )
s := LTrim(s, OmitChars := " `t`r`n") ; Remove the leading CrLf
if (Mode="VBA")
s := Trim(s, OmitChars := " `t`r`n")
else
s := Trim(s, OmitChars := " `t`r`n") . "`r`n"
s := RegExReplace( s, "{{°(.*?)}}" , "`r`n$1") ; Restore the Inline comments that were on their own line. BS/CK 7/8/2022
s := RegExReplace( s, "{{(.*?)}}", "$1") ; Restore T-SQL inline comments wrapped in braces. BS 5/3/2022
; s := RegExReplace( s, "«°(.*?)»" , "`r`n$1") ; Restore the Inline comments that were on their own line. BS/CK 7/8/2022
; s := RegExReplace( s, "«(.*?)»", "$1") ; Restore T-SQL inline comments wrapped in braces. BS 5/3/2022
Return s
}
;---------------------------------------------------------
RegExReplaceExclude(s, sFind, sReplace)
; A wrapper for RegExReplace() that will replace WORDS
; that are NOT contained in: Brackets, Double Quotes, Chevrons
; It will also ignore case.
; If you later want to search for non-words you'll need to change all the calls to this function.
; Note that this uses the "Best RegEx Trick Ever"
; BS/CK 7/8/2022
; s := RegExReplace( s, "i)(?![^\[^""«]*[\]^""»])(\bIS\b)", "IS")
{
;((\[.*?\])|(\"[^\"]*\")|(«[^«]*»))(*SKIP)(*FAIL)|as
; return RegExReplace( s, "i)((\[.*?\])|(\""[^\""]*\"")|({{[^{][^{]*}}))(*SKIP)(*FAIL)|\b(" . sFind . ")\b", sReplace)
return RegExReplace( s, "i)((\[.*?\])|(\""[^\""]*\"")|({{[^{][^{]*}}))(*SKIP)(*FAIL)|(\b" . sFind . "\b)", sReplace)
}
;---------------------------------------------------------
RegExReplaceExcludeNoWordBoundary(s, sFind, sReplace)
; A wrapper for RegExReplace() that will replace ANYTHING
; that is NOT contained in: Brackets, Double Quotes, Double Braces
; It will also ignore case.
; NOTE: This does not include word boundary "\b"
; If you later want to search for non-words you'll need to change all the calls to this function.
; Note that this uses the "Best RegEx Trick Ever"
; BS/CK 9/26/2022
; s := RegExReplace( s, "i)(?![^\[^""«]*[\]^""»])(\s,)", ",")
{
;((\[.*?\])|(\"[^\"]*\")|(«[^«]*»))(*SKIP)(*FAIL)|as
; return RegExReplace( s, "i)((\[.*?\])|(\""[^\""]*\"")|(«[^«]*»))(*SKIP)(*FAIL)|(" . sFind . ")", sReplace)
return RegExReplace( s, "i)((\[.*?\])|(\""[^\""]*\"")|({{[^{][^{]*}}))(*SKIP)(*FAIL)|(" . sFind . ")", sReplace)
}
;---------------------------
IsTextSelected()
; Function to return True if any text is currently selected.
; This does not work consistently with Outlook. BS 2/5/2020
{
ClipSaved:=ClipBoardAll
Clipboard=
SendInput ^c
ClipWait,0.2
If Clipboard=
{
; Nothing is selected.
Clipboard:=ClipSaved ; Restore the Clipboard
return, false
}else{
Clipboard:=ClipSaved ; Restore the Clipboard
return, true
}
}
Return
;------------------------------------------------------------
; Word Wrap function idea taken from here:
; https://autohotkey.com/board/topic/36988-word-wrap-in-an-output-variable/
wrap(String, maxWrapLength, separator = "", showSeparator = "")
{
endChar := "`n"
str1 := ""
; Comment out the next two lines if you want to start with text that is already formatted. BS 7/16/2020
;StringReplace String1, String, `r`n, %A_Space%, All
;StringReplace String1, String1, `n, %A_Space%, All
String1:=String
If (separator != "")
StringReplace, String1, String1, %separator%, %endChar%%separator%, All
Loop Parse, String1, `n
{
str := ""
length := 0
line := A_loopfield
Loop Parse, line, %A_Space%
{
lengthLoopfield := StrLen(A_loopfield)
length := length + lengthLoopfield +1
If (length > maxWrapLength)
{
str := str . endChar . A_loopfield . A_Space
length := lengthLoopfield + 1
}
Else
{
str := str . A_loopfield . A_Space
}
}
If (str1 = "")
str1 := str
Else
str1 := str1 . endChar . str
}
If (showSeparator = "")
StringReplace str1, str1, %separator%, , All
Return str1
}
How does it work?
A SQL statement is basically equivalent to a single line of code. It performs one action. That means carriage returns and formatting won't confuse it and cause a syntax error. One of the first steps this script performs is to remove all the existing whitespace and carriage returns. Then it follows a series of Find and Replace operations for simple words to add line breaks before/after as well as capitalize. Regular Expressions are used for more complicated areas. I had to work hard to come up with some of the Regex patterns that are used.
For example, in a WHERE clause I want to see a carriage return before each AND statement. On the other hand I don't want it to capitalize when the word "and" is in quotes or part of a column or table name like SELECT "Salt and Pepper" FROM [Spices and Seasonings]. I created a function that is called RegExReplaceExclude() that will perform a find/replace but ignores words that are contained Brackets and Double Quotes.
It may not be perfect but it can make a big difference in readability within a second of pressing the hotkey.
Cheers,
Ben
Pittsburgh, PA USA
Edited 12/15/2022: Formatting tweaks