Substring Search !

[Eddny]

Hello!

So let's say I have:

Dim MainString as string, Substring as string
Dim Result as Boolean

MainString ="ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"

Question: Is there an easier (faster) way to check whether MainString contains all elements of Substring without using loops?

Example 1: If Substring ="QRSTU|ABCDE|CGHMN" then Result = True since all elements of Substring are found in MainString.

Example 2: If Substring ="ABCDF|QRSTU|HPXYZ" then Result = False since not all elements of Substring are found in MainString.

Is there a way to perform these checks to see whether all the elements in Substring exist in MainString without using loops? I have thought about the Instr function, the Like operator, etc, However, it appears in each case I will have to use a loop.
Any help would be appreciated.

Eddny

 

[steve the fish]

Whats the problem with the loop?? As you are going to have to split your substring up then you have to use one.

 

[Eddny]

Whats the problem with the loop?? As you are going to have to split your substring up then you have to use one.

Steve - Yeah, thanks! I tried the loop route (where I first use Split() on the sub-string and loop to check each element's existence in MainString using Instr(). It works ok but need something faster, if possible. Have to do this repeatedly for a huge data set.

I was also wondering if there was a way to use the Like operator in a single-step check with some combination of a wildcard "*" search.

 

[steve the fish]

Not as i can see. You will have no choice but to split the substring but then you need to test each part hence need for a loop. The loop wont be slow by the way.

 

[Peter_SSs]

See if this non-looping method works for you.

Sub CheckAllSubstrings()
  Dim MainString As String, Substring As String
  Dim Result As Boolean
  Dim RX As Object

  MainString = "ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"
  Substring = "QRSTU|ABCDE|CGHMN"
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = MainString
  Result = Replace(RX.Replace(Substring, ""), "|", "") = vbNullString
End Sub

 

[steve the fish]

Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?

 

[Peter_SSs]

Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?

If that question is directed to me, then in a regular expression "pattern" line, a pipe acts as "or".

 

[Eddny]

See if this non-looping method works for you.

Sub CheckAllSubstrings()
  Dim MainString As String, Substring As String
  Dim Result As Boolean
  Dim RX As Object

  MainString = "ABCDE|ABCDF|CGHMN|QRSTU|CJKLM"
  Substring = "QRSTU|ABCDE|CGHMN"
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = MainString
  Result = Replace(RX.Replace(Substring, ""), "|", "") = vbNullString
End Sub

Peter - I don't have experience with regex but will try your code and let you know. I hope I don't have to activate anything else before using it. Thanks!

 

[Eddny]

Ive never used those expressions much. I presume looking at that it understands that the pipe is a seperator?

Steve - If your question was for me regarding the original post then, yes, the pipe "|" is being used as a delimiter or separator in my strings.

Thanks!

 

[Peter_SSs]

... will try your code and let you know.

I did assume that all sections of your substrings are 5 characters, like your samples. If that is not the case and/or there are other differences (eg upper/lower case or different delimiters) then more information and examples would help.