[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

VBScript REGEX list intersection

Hi,

I have two strings that actually are lists like :

S1 = "22,45,54,66,32"
S2 = "123,467,54,999"

I need to have a small function, using regex (no split table + loops) that sends back true or false if the lists intersect, which means they have an element in common.

In my example, S1 and S2 do intersects (54 common), but
S3 = "965,99"
should not intersect with S2 as 99 does not match 999.

Thanks for help
0
javilmer
Asked:
javilmer
  • 5
  • 5
1 Solution
 
käµfm³d 👽Commented:
Try this:

Function IsIntersection(string1, string2)
    temp = string1 & "|" & string2
    
    With New RegExp
        .Pattern = "\b(\d+)\b.*?\b\1\b"
        IsIntersection = .Test(temp)
    End With
End Function

Open in new window


Usage:
S1 = "22,45,54,66,32"
S2 = "123,467,54,999"
S3 = "965,99"

MsgBox IsIntersection(S1, S2)
MsgBox IsIntersection(S1, S3)

Open in new window

0
 
käµfm³d 👽Commented:
P.S.

The above works under the assumption that a single list does not contain duplicate numbers. If you want to ensure no duplicates, then you could do:

.Pattern = "\b(\d+)\b.*?\|.*?\b\1\b"

Open in new window

0
 
javilmerAuthor Commented:
Hello,

I got an error message : error BC300002 Type RegExp is not defined"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
käµfm³d 👽Commented:
Are you sure this is VBScript? I tested with the code above:

Screenshot
0
 
käµfm³d 👽Commented:
P.S.

If this is in fact VBA (e.g. Word, Excel, etc.), then you will need to add a reference to the "Visual Basic Scripting Library 5.5". I can't remember if that is the exact name, but it should be close enough for you to find it in the reference list.
0
 
javilmerAuthor Commented:
I use visual studio 2008 in an SQL server. Actually I do this to move from T-SQL function to assemblies (dll), for performance reasons
0
 
javilmerAuthor Commented:
I'm sorry, it is actually VB.NET
0
 
javilmerAuthor Commented:
I rewrite it to :

        Dim temp As String
        temp = s1 & "|" & s2

        Dim oRegExp As Object
        oRegExp = CreateObject("VBScript.RegExp")

        Dim IsIntersection As Boolean
        oRegExp.Pattern = "\b(\d+)\b.*?\b\1\b"
        IsIntersection = oRegExp.Test(temp)

        Return IsIntersection

but it is not working, with s1 = "a,b,c" and s2 = "c,d,e" it sends back false
0
 
käµfm³d 👽Commented:
If it's VB.NET, then you need the Regex class:

Function IsIntersection(ByVal string1 As String, ByVal string2 As String) As Boolean
    Dim temp As String = String.Concat(string1, "|", string2)
    
    Return System.Text.RegularExpressions.Regex.IsMatch(temp, \b(\d+)\b.*?\b\1\b)
End Function

Open in new window


but it is not working, with s1 = "a,b,c" and s2 = "c,d,e" it sends back false
That's because I coded it to work with the example you showed, which displays all numbers. We can correct that, though.

Function IsIntersection(ByVal string1 As String, ByVal string2 As String) As Boolean
	Dim temp As String = String.Concat(string1, "|", string2)
	
	Return System.Text.RegularExpressions.Regex.IsMatch(temp, "([^,]+).*?(?<=[,|])\1(?=,|$)")
End Function

Open in new window


Again, that is dependent on neither string containing duplicate values (internally), and it also depends on not having quoted strings (CSV-style).
0
 
javilmerAuthor Commented:
We are getting close !

but still a few problem, example :

s1 = 'aa,b,c'
s2 = 'a,d,e,f'

sends back true, should send back false a there is no common element (aa is not equal to a)

thanks for help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now