Finding multiple instance of a variable length string in a string

Question

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.

Bobort · Accepted Answer · 2014-11-05 21:46:28Z

1

I might approach this problem like so:

Remove characters that are not surrounded by spaces, but do not belong. In your example, the parentheses need to be removed.
Split the text using the space as a delimiter.
For each element in the split array, check the first character.
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

answered Nov 5, 2014 at 21:46

Bobort

3,24835 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mrbungle Over a year ago

++1 Thank you so much! Today is one of those days I just couldn't wrap my head around it. Now to make the rest of my ambitious project :-p

Collectives™ on Stack Overflow

Finding multiple instance of a variable length string in a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related