CODEWITHSUNDEEP
vbscript
MauriF
MauriF

Reputation: 682

VBScript RegEx pattern

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.

Upvotes: 0

Views: 89

Answers (1)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38775

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)

Upvotes: 1

Related Questions