SQL Replace IIF with CASE

Public Function Replace_IIF_with_CASE(ByVal strInput As String) As String
' Parse the passed string and find the first "IIF(" and parse it into
' a standard T-SQL CASE WHEN statement.  If a nested IIF is found,
' recurse and call this function again.
'
' Ben Sacherich - May 2016 - Feb 2017
'
' This supports:
'   IIF() embedded anywhere in the input string.
'   Nested IIF() statements.
'   The input string containing multiple IIF() statements on the same level.
'   Strings between open and close IIF parenthesis that contains literal commas or commas for other functions.
'       Example:  IIF(a=1, nz(b,0) , Left('xy,z',2))
'
' Be aware:
'   This does not optimize the CASE statement in places where a nested IIF could
'     be written as a single CASE statement.
'   It will fail if text inside IIF() contains the pipe character |. If you
'     need to process statements with this character, modify this routine
'     to use another temporary character for |.
'
' Try these in the Immediate window:
'   ? Replace_IIF_with_CASE("IIF(a=1, nz(b,0) , Left('xy,z',2))")
'   ? Replace_IIF_with_CASE("IIf(x='one',IIf(Abs(Z)=1,2,3),'three')")
'   ? Replace_IIF_with_CASE("IIF(a=1,'1st)', '2nd)')")
'   ? Replace_IIF_with_CASE("SELECT Name, IIF(Gender='M', 'Boy', 'Girl') FROM Students")
'
' BS 2/24/2017: After never getting the RegEx to work and getting advice that it
'   was too complex, I developed a new method that does not use RegEx.
'
' How this works:
'   Find "IIF(" in the passed string.  Return original string if not found.
'   Search for the matching closing parenthesis.
'   When the match is found, recurse and make sure an "IIF(" is not nested.
'   After recursing, replace the IIF with a CASE statement.
'       xxxxx Use a regular expression to find the commas inside the IIF that
'       xxxxx are not contained within single quotes or parenthesis.
'       - Once I find the inner part of an IIF this will use the Split function
'         to delimit by commas "," into an array.
'       - Then it looks at each array element. If it contains an odd number of
'         single or double quote characters or different number of opening and
'         closing parenthesis, it combines the array element part with the next
'         part and tests again.
'       - When there are matched single/double quotes and equivalent number of
'         parenthesis it holds that part and appends the "|" character.  This
'         means that it has identified one of the 3 parameters that is passed
'         to the IIF function.
'       - Then it splits the string by the "|" character into three pieces
'         and builds the CASE statement.
'   Continue searching the passed string for another occurrence of "IIF(" (not nested).
'
' Old issues that should now be resolved:
'   String containing open or close parenthesis inside of quotes (as a literal).
'       This is a problem if they are unpaired inside the IIF function.
'       Example:  IIF(a=1,'1st)', '2nd)')
'       I had this working until I started using RegEx to exclude commas
'       within function calls like "Left('abc',2)".
'       An error will be returned in the string if this condition is found.
'     http://regexr.com/3dc2u
'     https://regex101.com/r/qH0wD8/2
'

    Dim lngFuncStart As Long
    Dim lngPosition As Long
    Dim intStack As Integer
    Dim strFunction As String
    Dim strChar As String
    Dim strQuoteChar As String
    Dim bolInQuotes As Boolean
    Dim strSplit() As String
    Dim ReturnValue As String
    Dim bolHint As Boolean

    On Error GoTo ErrorHandler

    strFunction = "IIF("
    strQuoteChar = "'"      ' Define the style of quotes to look for and exclude.
    bolInQuotes = False     ' We are currently not inside quotes.

    lngFuncStart = InStr(1, strInput, strFunction, vbTextCompare)

    If lngFuncStart > 0 Then
        lngFuncStart = lngFuncStart + Len(strFunction)
        intStack = 1
        lngPosition = lngFuncStart

        Do While lngPosition <= Len(strInput)
            ' Use a WHILE loop instead of a FOR loop because the current and end positions will change inside the loop.

            strChar = Mid(strInput, lngPosition, 1)

            If strChar = strQuoteChar Then
                bolInQuotes = Not bolInQuotes
                ' Move on to the next character

            ElseIf bolInQuotes = False Then
                ' We are currently not inside quotes.

                Select Case strChar
                    Case ")"
                        ' Closing a group
                        intStack = intStack - 1
                    Case "("
                        ' Starting new group
                        intStack = intStack + 1
                End Select

                If intStack = 0 Then    ' Found closing parenthesis.

                    ' See if there is a nested match.  ### Recursive ###
                    ReturnValue = Replace_IIF_with_CASE(Mid(strInput, lngFuncStart, lngPosition - lngFuncStart))

                    ' Begin parsing commas.
                    strSplit() = Split(ReturnValue, ",")

                    Dim strPart As String
                    Dim strRebuilt As String
                    Dim i As Integer

                    strRebuilt = ""
                    If UBound(strSplit()) > 2 Then    ' There are more than 2 commas.  Piece together the parts.

                        strPart = strSplit(0)
                        For i = 1 To UBound(strSplit)
                            ' Debug.Print i, strPart, strRebuilt

                            If UBound(Split(strPart, "'")) Mod 2 = 0 _
                               And UBound(Split(strPart, """")) Mod 2 = 0 _
                               And UBound(Split(strPart, "(")) = UBound(Split(strPart, ")")) Then
                                ' Number of single quotes is Even or Zero (matched)
                                ' Number of double quotes is Even or Zero (matched)
                                ' Number of parenthesis is matched

                                ' Add the "|" symbol where the IIF should have commas.
                                strRebuilt = strRebuilt & "|" & strPart
                                strPart = strSplit(i)
                            Else
                                strPart = strPart & "," & strSplit(i)
                            End If
                        Next
                        ReturnValue = Mid(strRebuilt & "|" & strPart, 2)

                        strSplit() = Split(ReturnValue, "|")
                    End If

                    If UBound(strSplit) = 2 Then
                        ' IIF has 3 parameters and is the normal case.

                        ' Check to see if this could be a good candiadate for the COALESCE() or ISNULL()
                        ' functions instead of CASE WHEN.
                        ' https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017
                        If InStr(1, Trim(strSplit(0)), " IS NOT NULL", vbTextCompare) > 1 _
                          Or InStr(1, Trim(strSplit(0)), " IS NULL", vbTextCompare) > 1 Then
                            bolHint = True
                        End If

                        '--- Replace the IIF statement with CASE WHEN ---
                        ' CASE statement help:  https://msdn.microsoft.com/en-us/library/ms181765.aspx
                        ReturnValue = "(CASE WHEN " & Trim(strSplit(0)) & " THEN " & Trim(strSplit(1)) & " ELSE " & Trim(strSplit(2)) & " END)"
                        If Right(Mid(strInput, 1, lngFuncStart - Len(strFunction) - 1), 2) = vbCrLf Then
                            ' Don't bother to add a CrLf
                            ' Stop
                        Else
                            ' Add a CrLf before the CASE statement to make identification easier.
                            ' Comment this out if you don't want it added.
                            ReturnValue = vbCrLf & ReturnValue
                        End If

                        If bolHint Then
                            ' Add the hint to the SQL.  You may want to disable this in your implementation.
                            ReturnValue = ReturnValue & vbCrLf & "/* This CASE may be better using ISNULL() or COALESCE() */ "
                        End If

                        strInput = Mid(strInput, 1, lngFuncStart - Len(strFunction) - 1) & ReturnValue & Mid(strInput, lngPosition + 1)

                        'Debug.Print ReturnValue
                    Else
                        ' Something is wrong.  Return the original IIF statement.
                        ' Known issues:
                        '       Text inside IIF() contained pipe character |
                        '       Text contained unmatched parenthesis, maybe inside of a literal string like '1st)'
                        '       Three parameters were not passed to the IIF()
                        ReturnValue = "IIF(" & ReturnValue & ") /*### Unable to parse IIF() ###*/ "
                        strInput = Mid(strInput, 1, lngFuncStart - Len(strFunction) - 1) & ReturnValue & Mid(strInput, lngPosition + 1)

                    End If

                    '--- Check to see if there is another function call following the one just addressed. ---
                    lngFuncStart = InStr(lngFuncStart + Len(ReturnValue) - Len(strFunction), strInput, strFunction, vbTextCompare)
                    If lngFuncStart > 0 Then
                        ' Another IIF function call is at the same level as the one just processed.
                        lngFuncStart = lngFuncStart + Len(strFunction)
                        intStack = 1
                        lngPosition = lngFuncStart
                    Else
                        ReturnValue = strInput
                        Exit Do
                    End If

                End If
            End If
            lngPosition = lngPosition + 1
        Loop

    Else
        ' Function not found in passed string.
        ReturnValue = strInput
    End If

    Replace_IIF_with_CASE = ReturnValue

ExitHere:
    Exit Function

ErrorHandler:

    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure Replace_IIF_with_CASE()" _
         & vbCrLf & "Input:  " & strInput, vbExclamation, "Error"

    Resume ExitHere

End Function