If you for example would like to do a count(0) on the from part returned by the function, group and order by clauses should be stripped if present. I've added this functionality as an optional parameter. I've also updated the function to split the source query on union, intersect and except also taking into account that these keywords can be included in the query as an alias. Another important change is that square brackets [ ] are also taken into account as alias indicators.
Below is the new function, a bit more complicated as the first function and it probably needs some tidying up, but that's for when I've got some more time. I've also included an (absolutely ridiculous) source test query and the result the function produces
Public Function ProfelGetFromPartOfAQuery(SourceQuery As String, Optional StripAfterWhere As Boolean = True) As List(Of String)
Dim nestLevel As Integer = 0
Dim blnQuoteOpen As Boolean = False
Dim blnBracketOpen As Boolean = False
Dim lstReturn As New List(Of String)
Dim iIndex As Integer = 0
Dim strWord As String = ""
Dim queries As New List(Of String)
Dim tmpQuery As String = SourceQuery
'First split the query on certain keywords, union, execpt, intersect
'This can't be done with a simple split because the keywords could also be used as aliases
'The string is split on except and intersect but the function doesn't tell which from part is
'from the except or intersect, this falls outside the scope of this function
For Each c As Char In SourceQuery
iIndex += 1
If c = "'"c And blnQuoteOpen = False Then
blnQuoteOpen = True
Else
If c = "'"c And blnQuoteOpen = True Then
blnQuoteOpen = False
End If
End If
If c = "["c And blnBracketOpen = False Then
blnBracketOpen = True
Else
If c = "]"c And blnBracketOpen = True Then
blnBracketOpen = False
End If
End If
If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel += 1
End If
If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel -= 1
End If
If c = " "c Then
strWord = ""
Else
strWord &= c
End If
If nestLevel = 0 Then
Select Case strWord
Case "union", "except", "intersect"
queries.Add(Mid(tmpQuery, 1, iIndex - strWord.Length))
tmpQuery = Mid(tmpQuery, iIndex + 1, tmpQuery.Length - iIndex)
iIndex = 0
End Select
End If
Next
If queries.Count = 0 Then
queries.Add(SourceQuery)
Else
queries.Add(tmpQuery)
End If
'Analyze the seperate queries
For Each query As String In queries
iIndex = 0
strWord = ""
blnQuoteOpen = False
blnBracketOpen = False
For Each c As Char In query
iIndex += 1
If c = "'"c And blnQuoteOpen = False Then
blnQuoteOpen = True
Else
If c = "'"c And blnQuoteOpen = True Then
blnQuoteOpen = False
End If
End If
If c = "["c And blnBracketOpen = False Then
blnBracketOpen = True
Else
If c = "]"c And blnBracketOpen = True Then
blnBracketOpen = False
End If
End If
If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel += 1
End If
If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel -= 1
End If
If c = " "c Then
strWord = ""
Else
strWord &= c
End If
If strWord.ToLower = "from" And nestLevel = 0 Then
Dim strtmp As String = Mid(query, iIndex - 3, query.Length - (iIndex - 4)).ToLower
'If you want to strip eveything after the where set StripAfterWhere = true
'this can be used if you want to do a count(0) on the from part returned
If StripAfterWhere = True Then
Dim iGroup As Integer = ProfelGetPartOfAQuery("group", strtmp)
If iGroup <> 0 Then
lstReturn.Add(Mid(strtmp, 1, iGroup))
Else
Dim iOrderBy As Integer = ProfelGetPartOfAQuery("order", strtmp)
If iOrderBy <> 0 Then
lstReturn.Add(Mid(strtmp, 1, iOrderBy))
Else
lstReturn.Add(strtmp)
End If
End If
Else
lstReturn.Add(strtmp)
End If
Exit For
End If
Next
Next
Return lstReturn
End Function
Public Function ProfelGetPartOfAQuery(part As String, SourceQuery As String) As Integer
Dim nestLevel As Integer = 0
Dim blnQuoteOpen As Boolean = False
Dim blnBracketOpen As Boolean = False
Dim lstReturn As New Integer
Dim iIndex As Integer = 0
Dim strWord As String = ""
For Each c As Char In SourceQuery
iIndex += 1
If c = "'"c And blnQuoteOpen = False Then
blnQuoteOpen = True
Else
If c = "'"c And blnQuoteOpen = True Then
blnQuoteOpen = False
End If
End If
If c = "["c And blnBracketOpen = False Then
blnBracketOpen = True
Else
If c = "]"c And blnBracketOpen = True Then
blnBracketOpen = False
End If
End If
If c = "(" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel += 1
End If
If c = ")" AndAlso blnQuoteOpen = False AndAlso blnBracketOpen = False Then
nestLevel -= 1
End If
If c = " "c Then
strWord = ""
Else
strWord &= c
End If
If strWord.ToLower = part And nestLevel = 0 Then
lstReturn = iIndex - (part.Length)
Exit For
End If
Next
Return lstReturn
End Function
The (absolutely ridiculous) query
select replace(replace(eb001.kltnr, '(',' '),']','')
from eb001
left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr
left outer join ea007 on eb001.taalc = ea007.taalc
left outer join ea012 on eb001.pscdx = ea012.pscdx
where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'
or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN')
group by replace(replace(eb001.kltnr, '(',' '),']','')
order by (select distinct replace(replace(eb001.kltnr, '(',' '),']','')
from eb001
left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr
left outer join ea007 on eb001.taalc = ea007.taalc
left outer join ea012 on eb001.pscdx = ea012.pscdx
where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'BENTHIN'))
The result
from eb001
left outer join eb008 as [''''group)(]]'] on isnull(eb001.kltnr,'') = [''''group)(]]'].kltnr
left outer join ea007 on eb001.taalc = ea007.taalc
left outer join ea012 on eb001.pscdx = ea012.pscdx
where ([''''group)(]]'].bedrf = '03' or [''''group)(]]'].bedrf = '10' or eb001.intco = 'j'
or (select top 1 slbkh as 'group by' from eb001 group by slbkh) = 'benthin')
No comments:
Post a Comment