SQL Pretty Formatter

Post your working scripts, libraries and tools for AHK v1.1 and older
Ben
Posts: 22
Joined: 03 Oct 2016, 07:26

SQL Pretty Formatter

Post by Ben » 09 Aug 2022, 00:35

I have created a hotkey script that will format the selected SQL statement by adding carriage returns and indents. This greatly improves my sanity when I'm trying to troubleshoot a SQL query.

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;
Being converted to this much prettier format just by selecting the text and pressing a hotkey.

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;
By the nature of this being a hotkey you can use it in any text editor or web page. There are some comments in the script that suggest other behaviors such as where the comma is placed for each column of a SELECT statement. Please share any additional syntax handlers that you need. I have a variant of this script that format a query in quotes so it can be easily added to VB code.

I've been using this for a few years and I'm glad to give something back to this community. :D
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
Last edited by Ben on 15 Dec 2022, 15:07, edited 4 times in total.

User avatar
boiler
Posts: 16771
Joined: 21 Dec 2014, 02:44

Re: SQL Pretty Formatter

Post by boiler » 09 Aug 2022, 03:41

Very nice. I will make regular use of this. It happens to match my preferred formatting, other than I typically put the comma at the end of the line (although that has been a subject of debate), but it sounds like that is configurable.

Thanks for sharing! :thumbup:

Ben
Posts: 22
Joined: 03 Oct 2016, 07:26

Re: SQL Pretty Formatter

Post by Ben » 10 Aug 2022, 20:57

boiler wrote:
09 Aug 2022, 03:41
Very nice. I will make regular use of this. It happens to match my preferred formatting, other than I typically put the comma at the end of the line (although that has been a subject of debate), but it sounds like that is configurable.
Yes, on lines 226-227 you can adjust whether the comma is a prefix or suffix on each line. I used to like the look of the comma at the end but a friend showed me that having the comma at the beginning makes it easier to comment out a column (as long as it's not the first column).

I have been a fan of Poor Man's T-SQL Formatter but wanted a way to format the SQL inside any editor, including Microsoft Access, email, and web pages. Access embeds a lot of parenthesis in the FROM clause and and Poor Mans turns that into a lot of indents which makes it hard to read the FROM statement.

I don't expect many AutoHotkey users work with SQL, but I do think SQL developers can make use of AutoHotkey.

User avatar
boiler
Posts: 16771
Joined: 21 Dec 2014, 02:44

Re: SQL Pretty Formatter

Post by boiler » 10 Aug 2022, 21:38

Ben wrote: Yes, on lines 226-227 you can adjust whether the comma is a prefix or suffix on each line.
Thanks.

Ben wrote: I used to like the look of the comma at the end but a friend showed me that having the comma at the beginning makes it easier to comment out a column (as long as it's not the first column).
I thought I heard something like this too, but it seems to me that any column in the middle of a query can just as easily be easily commented out whether the comma is at the beginning or end. It’s still just preceding the line with --, or is there some trick I’m missing?

Ben wrote: I don't expect many AutoHotkey users work with SQL, but I do think SQL developers can make use of AutoHotkey.
I agree. In addition to tools, I found several occasions to pre- or post-process data/tables in ways that are easy for AHK (or most any programming language) to handle but are quite complicated in pure db queries, if possible at all.

Ben
Posts: 22
Joined: 03 Oct 2016, 07:26

Re: SQL Pretty Formatter

Post by Ben » 11 Aug 2022, 00:37

Ben wrote: ↑
I used to like the look of the comma at the end but a friend showed me that having the comma at the beginning makes it easier to comment out a column (as long as it's not the first column).
I thought I heard something like this too, but it seems to me that any column in the middle of a query can just as easily be easily commented out whether the comma is at the beginning or end. It’s still just preceding the line with --, or is there some trick I’m missing?
I wasn't going to push my method any further but because you asked I'll share my opinion.

Advantages of putting a comma at the beginning instead of the end:
  • In practice, when you're modifying a query you're more likely to want to comment out the last line of your SELECT than the first line. This means you don't need to modify the line above the commented line by adding/removing a trailing comma.
  • Having the comma at the beginning makes all of the commas line up vertically. If you have to paste in some fields it's a lot easier to see when commas are missing and easier to add them.
  • In bigger queries and UNION queries the comma prefix makes the SELECT section of the query visually stand out from other sections.
That being said, formatting with the comma at the end is still better than inconsistent or no formatting.
Last edited by Ben on 11 Aug 2022, 09:42, edited 1 time in total.

User avatar
boiler
Posts: 16771
Joined: 21 Dec 2014, 02:44

Re: SQL Pretty Formatter

Post by boiler » 11 Aug 2022, 05:37

Good points. Thanks.

Ben
Posts: 22
Joined: 03 Oct 2016, 07:26

Re: SQL Pretty Formatter

Post by Ben » 27 Sep 2022, 22:08

I updated the code today in the original post. It now has better handling of putting spaces around commas. It will skip commas that are inside quotes, parenthesis, brackets, and double braces.
There is also a new function called RegExReplaceExcludeNoWordBoundary() to allow Regex calls to exclude some common syntax conditions.

AHKStudent
Posts: 1472
Joined: 05 May 2018, 12:23

Re: SQL Pretty Formatter

Post by AHKStudent » 30 Sep 2022, 01:01

very nice script, extremely helpful

Post Reply

Return to “Scripts and Functions (v1)”