Search an array?

Is it possible to search for a string in a string array? I saw IndexOf, but that seems to only be able to search the array item’s position in the array

If all you want to know is if a string is within an array, you can first Join the array into one big string and then use IndexOf or Instr to see if the string is there.

If you need to know the index of the member in the array, you got to loop through it.

No, it searches for the string in the array. It returns the index position or -1 if it’s not in the array. That sounds like what you’re asking.

Or are you looking for a substring in an array of strings? If so, then Michel’s approach is good. It tells you whether the substring is present, but not in which element.

Thanks guys. I just need to see if it is present. I am working off of a database table that pulls in unique email addresses based on a unique registration code. One email address can be associated with two registration codes (student was registered at the school’s computer as well as at their home computer). I am filling a listbox with this data and do not want duplicate entries. The email addresses are the unique as are the registration codes.

I’m trying to work my sql statement to say find all email addresses based on each reg code, and then add them to the listbox only if they’re not aready present. If it’s not, i add to an array. Code loops back through for the next reg code/email address, and if the email is present, it will not add again.

I didn’t really get to test this much yet (Michel’s suggestion). I see that Instr returns an integer. If that is the case, would this actually be finding the duplicates and not adding them to the listbox? How would that be?

instr searches a string for a substring

indexof finds a value in an array (the whole value in the case of a string)

Since you’re searching for the entire string, and not as substring, use IndexOf. It returns an integer value

-1 means the string was not found

= 0 means the string was found

Then let the DB do that job.

dim SQL as string="Select distinct(email) from someTable where email is not in (‘’,‘’,…)

Databases are very good at that kind of thing.

I agree with Maximiliam. What’s wrong with the existing database!?

I get the impression your set up is similar to this:

intID – counter
strEmail – text
strCodeHome – text
strCodeSchool – text

The (non-working) code would be similar to:

SQL = "SELECT intID, strEmail, strCodeHome, strCodeSchool FROM [users] WHERE strEmail =  " + TextField1.text

Then the traditional rs- workaround… checking if values are empty or true or something.
I must be missing something, because I can’t see where arrays would be part of the game! Enlighten me! :slight_smile:

Or… do you load the entire user DB in an array? Even if you do, for another purpose, I don’t see how arrays can be your matching partner in this specific task as described above!

Got it! Thanks for your help everyone!

Here’s the story as to why I thought it be best to use an array in addition to the db.

  • Students’ name, email, registration code, plus some other data is saved to a MySQL server
  • I am using the DISTINCT SQL statement to pull the unique email addresses based on their registration code
sql = "SELECT DISTINCT Email FROM emailresults WHERE RegCode = '" + lbxRegCode.Cell(i, 0) + "'"
  • Problem was, student email is registered at school with reg code 1234 and at home with code ABCD, so there were 2 instances of this email address showing up in the listbox


  • During the loop to fill the listbox, first check to see if the email address is already present. If it’s not, add the student data in the listbox, then also add the email address to an array
  • Code loops back around for the next entry, but this time it will check to see if the email address is already present in the array

[code] dim combinedEmail as String = Join(CheckEmailDups)

    if InStr(combinedEmail, data.Field("Email").StringValue) <1 then[/code]
  • if Instr = 0, email address is not present, and the student info will be added to the box
  • If Instr = something other than 0, email is present, and info not added

I’m sure there was another way to accomplish this, but this works!

I agree with you! :slight_smile:
I don’t see the unique part in all of this.

I strongly suggest you rebuild your service today, while you still remember how it works.
Eventually, you will forget… “Eventually” tend to arrive painful fast and when you least expect it!