Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: custom function: extracting SQL

    i wrote a custom function to extract different clauses of an sql.
    Everything works fine except for the FROM clause. the result always shows the from clause and some or all of the the next clause.
    here's the code:
    Public Function ExtrSQL(SQL As String, Clause As String) As String

    Select Case Clause
    Case "select"
    ExtrSQL = Mid(SQL, InStr(SQL, "SELECT"), InStr(SQL, "FROM") - 1)
    Case "from"
    If InStr(SQL, "WHERE") <> 0 Then
    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"), InStr(SQL, "WHERE") - 1)
    Else
    If InStr(SQL, "GROUP BY") <> 0 Then
    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"), InStr(SQL, "GROUP BY") - 1)
    Else
    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"), Len(SQL))
    End If
    End If
    Case "where"
    If InStr(SQL, "WHERE") <> 0 Then
    If InStr(SQL, "GROUP BY") <> 0 Then
    ExtrSQL = Mid(SQL, InStr(SQL, "WHERE"), InStr(SQL, "GROUP BY" - 1))
    Else
    ExtrSQL = Mid(SQL, InStr(SQL, "WHERE"), Len(SQL))
    End If
    Else
    ExtrSQL = ""
    End If
    Case "group by"
    If InStr(SQL, "GROUP BY") <> 0 Then
    ExtrSQL = Mid(SQL, InStr(SQL, "GROUP BY"), Len(SQL))
    Else
    ExtrSQL = ""
    End If
    End Select

    Debug.Print "ExtrSQL(SQL, " & "'" & Clause & "'" & ")" & " = " & ExtrSQL
    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think pehaps you functin shoud be something like this
    Code:
    Public Function ExtrSQL(SQL As String, Clause As String) As String
    
        Select Case Clause
            Case "select"
                ExtrSQL = Mid(SQL, InStr(SQL, "SELECT"), InStr(SQL, "FROM") - 1)
            Case "from"
                If InStr(SQL, "WHERE") <> 0 Then
                    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"), (InStr(SQL, "WHERE") - 1) - InStr(SQL, "FROM"))
                ElseIf InStr(SQL, "GROUP BY") <> 0 Then
                    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"), (InStr(SQL, "GROUP BY") - 1) - InStr(SQL, "FROM"))
                Else
                    ExtrSQL = Mid(SQL, InStr(SQL, "FROM"))
                End If
            Case "where"
                If InStr(SQL, "WHERE") <> 0 Then
                    If InStr(SQL, "GROUP BY") <> 0 Then
                        ExtrSQL = Mid(SQL, InStr(SQL, "WHERE"), (InStr(SQL, "GROUP BY" - 1)) - InStr(SQL, "WHERE"))
                    Else
                        ExtrSQL = Mid(SQL, InStr(SQL, "WHERE"))
                    End If
                Else
                    ExtrSQL = ""
                End If
            Case "group by"
                If InStr(SQL, "GROUP BY") <> 0 Then
                    ExtrSQL = Mid(SQL, InStr(SQL, "GROUP BY"))
                Else
                    ExtrSQL = ""
                End If
        End Select
    
    Debug.Print "ExtrSQL(SQL, " & "'" & Clause & "'" & ")" & " = " & ExtrSQL
    End Function
    The second 'length' agument of Mid() should be the number of character to return (unless omitted), not the position where you want the returned string to finish!?


    MTB

  3. #3
    Join Date
    Aug 2010
    Posts
    7
    how silly of me..
    thnx a lot, that solved the problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •