Break apart a SQL stored procedure stirng

We're building an ASP.Net app (VB) where one part of the process needs to take a command string and break it apart into its constituent pieces: stored procedure name, and each argument name with its value (may have one or more args, or none).  A string may look like any of the following:

 ;

EXEC stpMyProcName StartDate='12/1/2007',EndDate='12/31/2007'

Thank you very much Jeff, we'll bang this guy around and see what we can do with it.  Always appreciate the help!!

 

-- Andrew

 

On 12/20/07, Jeff < jeff.hillmangmail.com">jeff.hillmangmail.com> wrote:

Andrew,

I think the regular expression you want is this one:

EXECs+(?<Proc>w+)(s+((?<Param>w+)=(?<Value&gt;[^,]+),?)*)?

One part that might need tweaking is the Value group.&nbsp; I made it match
anything that isn't a comma, since commas are used to separate
parameters, but if a parameter value can contain a comma, you might
need to adjust that part of the expression.

Below is the code I used to test this expression.

Dim procRegex As Regex
procRegex = New Regex( _
"EXECs+(?<Proc>w+)(s+((?<Param>w+)=(?<Value&gt;[^,]+),?)*)?")

Dim procMatch As Match

Dim testStrings() As String
testStrings = New String() { _
"EXEC stpMyProcName StartDate='12/1/2007',EndDate=&#39;12/31/2007'&quot;, _
"EXEC stpMyProcName", _
"EXEC stpMyProcName FullName=&#39;John Q. Smith'", _
"EXEC stpMyProcName ID=14,Age=21,Phone=NULL,Date='12/31/2007&#39;"}

For Each testString As String In testStrings
 &nbsp; procMatch = procRegex.Match(testString)

&nbsp;  If (procMatch.Success) Then
 ; &nbsp; &nbsp;  Console.WriteLine(&quot;*************************";)
 &nbsp; &nbsp;   Console.WriteLine(String.Format("Procedure:{0,15}", _
 &nbsp; &nbsp;   ; &nbsp;  procMatch.Groups.Item("Proc").Value))

&nbsp; &nbsp; &nbsp;  Dim paramGroup = procMatch.Groups.Item("Param")
&nbsp; &nbsp;   ; Dim valueGroup = procMatch.Groups.Item("Value")

&nbsp;   ; &nbsp; If (paramGroup.Success ) Then
 ; &nbsp; &nbsp; &nbsp; &nbsp;  Console.WriteLine()
&nbsp;   ; &nbsp; &nbsp; &nbsp; Console.WriteLine(&quot;Parameter &nbsp; &nbsp; &nbsp; &nbsp;   Value";)
 &nbsp; &nbsp;   ; &nbsp;  Console.WriteLine(&quot;-------------------------";)

 &nbsp;   ; &nbsp; &nbsp;  For i As Integer = 0 To paramGroup.Captures.Count - 1
 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp;  Console.WriteLine( _
 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; String.Format("{0,-10}{1,15}", _
 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; paramGroup.Captures.Item(i).Value, _
 &nbsp; &nbsp;   ; &nbsp; &nbsp; &nbsp; &nbsp;   ; valueGroup.Captures.Item (i).Value))
 ; &nbsp; &nbsp; &nbsp; &nbsp;  Next
 ; &nbsp; &nbsp;  End If

   ; &nbsp;  Console.WriteLine(&quot;*************************";)
 &nbsp; &nbsp;   Console.WriteLine()
&nbsp;  End If
Next

I hope this helps.

Jeff

On Dec 21, 12:24 am, "Andrew Raastad&quot; < andrew...gmail.com">andrew...gmail.com> wrote:
>; We're building an ASP.Net app (VB) where one part of the process needs to
> take a command string and break it apart into its constituent pieces: stored
&gt; procedure name, and each argument name with its value (may have one or more
> args, or none).&nbsp; A string may look like any of the following:
>
> EXEC stpMyProcName StartDate='12/1/2007',EndDate=&#39;12/31/2007'
&gt; &nbsp;EXEC stpMyProcName
>&nbsp; EXEC stpMyProcName FullName=&#39;John Q. Smith'
>  EXEC stpMyProcName ID=14,Age=21,Phone=NULL,Date='12/31/2007&#39;
> etc.
>
> The expression we have come up with so far sort of breaks it apart, but not
> quite the way we want:
> (?!<=EXECs)(.*s)(?<Parms&gt;w+='w+',*)*
&gt;
> This gives two groups:
&gt; 1: [stpMyProcName]
> Parms: [everything after the first or nothing if no arguments]
>
>; What we would like to have is X number of groups where the first group is
> the Procedure Name and the remaining X number of groups be each Argument,
> like such (using the first example above):
> Proc: [stpMyProcName]
> StartDate: 12/1/2007
> EndDate: 12/31/2007
>
> or if there are no Arguments, just give one group, that of the Procedure
> Name.
>
> Is this possible?&nbsp; If so, could someone lend a hand with the expression?
> Or, if what I have said is not clear, please ask and I will try to explain
&gt; it another way.
>
> Thanks!
&gt;
> -- Andrew


--
My new philosophy!
Life's journey is not to arrive at the grave safely in a well preserved body, but rather to skid in sideways, totally worn out, shouting "...holy shit...what a ride!";
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Regex" group.
To post to this group, send email to regexgooglegroups.com
To unsubscribe from this group, send email to regex-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/regex
-~----------~----~----~----~------~----~------~--~---