VBScript RegEx pattern

Question

I need a pattern that does a very specific thing but after hours I can't achieve te expected result.

Sample string:

SELECT col1 FROM tbl1 WHERE col1 = (SELECT col2 FROM tbl2 WHERE col2=col2)

Expected result:

FROM tbl1 WHERE col1 = (SELECT col2 FROM tbl2 WHERE col2=col2)

    -> tbl1
    -> WHERE col1 = (SELECT col2 FROM tbl2 WHERE col2=col2)

Actual pattern:

FROM\s+([^\s,]+)[\s\S]+(WHERE[\s\S]+)

Actual result:

FROM tbl1 WHERE col1 = (SELECT col2 FROM tbl2 WHERE col2=col2)

    -> tbl2
    -> WHERE col2=col2)

I have tried using look ahead and other things but I can't make it group from the first 'WHERE'.

Note: Between 'tbl1' and 'WHERE' should match everything posible, not just a space.

Note2: It should group all after first 'WHERE' even if there is not a where later on.

You should publish a list of representative input/output pairs and at least a hint at your strategy to produce the outputs from the match objects. — Ekkehard.Horner
– Ekkehard.Horner, Commented Jul 3, 2017 at 16:28

Ekkehard.Horner · Accepted Answer · 2017-07-03 16:40:36Z

1

Until more details are published, I claim that a non greedy pattern will solve the problem:

Option Explicit

Dim r : Set r = New RegExp
'r.Pattern = "FROM\s+([^\s,]+)[\s\S]+(WHERE[\s\S]+)"
r.Pattern = "FROM\s+([^\s,]+)[\s\S]+?(WHERE[\s\S]+)"
'Dim s : s = "SELECT col1 FROM tbl1 WHERE col1 = (SELECT col2 FROM tbl2 WHERE col2=col2)"
Dim s : s = "SELECT col1 FROM tbl1 WHERE col1 = (No  W h e r e  here)"
Dim m : Set m = r.Execute(s)
If 1 = m.Count Then
   Dim t : t = Join(Array("From", m(0).SubMatches(0), m(0).SubMatches(1), _
                          vbCrLf, vbCrLf, "->", m(0).SubMatches(0), vbCrLf, "->", m(0).SubMatches(1)))
   WScript.Echo s
   WScript.Echo t
Else
   WScript.Echo "no match"
End If

output:

cscript 44890052.vbs
SELECT col1 FROM tbl1 WHERE col1 = (No  W h e r e  here)
From tbl1 WHERE col1 = (No  W h e r e  here)

 -> tbl1
 -> WHERE col1 = (No  W h e r e  here)

answered Jul 3, 2017 at 16:40

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

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

1 Comment

MauriF Over a year ago

It worked nicely, even with the second 'where'. Thx!

Collectives™ on Stack Overflow

VBScript RegEx pattern

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