CODEWITHSUNDEEP
excelvba
mrbungle
mrbungle

Reputation: 1931

Finding multiple instance of a variable length string in a string

I'm trying to extract my parameters from my SQL query to build my xml for an SSRS report. I want to be able to copy/paste my SQL into Excel, look through the code and find all instances of '@' and the appropriate parameter attached to it. These paramaters will ultimately be copied and pasted to another sheet for further use. So for example:

where DateField between @FromDate and @ToDate
      and (BalanceFiled between @BalanceFrom and @BalanceTo
            OR BalancdField = @BalanceFrom)

I know I can use Instr to find the starting position of the first '@' in a line but how then do I go about extracting the rest of the parameter name (which varies) and also, in the first two lines of the example, finding the second parameter and extracting it's variable lenght? I've also tried using the .Find method which I've been able to copy the whole line over but not just the parameters.

Upvotes: 0

Views: 96

Answers (1)

Bobort
Bobort

Reputation: 3218

I might approach this problem like so:

  1. Remove characters that are not surrounded by spaces, but do not belong. In your example, the parentheses need to be removed.
  2. Split the text using the space as a delimiter.
  3. For each element in the split array, check the first character.
  4. If it is "@", then the parameter is found, and it is the entire value in that part of the array.

My user-defined function looks something like this:

Public Function GetParameters(ByRef rsSQL As String) As String
    Dim sWords() As String
    Dim s As Variant
    Dim sResult As String

    'remove parentheses and split at space
    sWords = Split(Replace(Replace(rsSQL, ")", ""), "(", ""), " ")

    'find parameters
    For Each s In sWords
        If Left$(s, 1) = "@" Then
            sResult = sResult & s & ", "
        End If
    Next s

    'remove extra comma from list
    If sResult <> "" Then
        sResult = Left$(sResult, Len(sResult) - 2)
    End If

    GetParameters = sResult
End Function

Upvotes: 1

Related Questions