Tech Tip!

How to find any value in a string and then determine the multivalue number in which it exists.

Let's say that you have a multi-valued field of telephone numbers in a customer record, and as has happened so frequently in the last few years, new area codes have been assigned to specific telephone exchanges. In order to update and change the area code, the INDEX/DCOUNT combo might be useful.

Example:
POS = INDEX(STRING,VALUE,1)
POS = DCOUNT(STRING[1,POS],CHAR(253))

STRING - the string we are going to search in (ex: attribute)
VALUE - the value we are looking for
POS - multivalue position

First line is going to give us position in our string, the second one will count the number of multivalues up to length specified by POS. If the returned value is 0(zero) - value has not been found.

Note: the INDEX/DCOUNT combo is only a suggested format of searching for a partial match in a multivalued field, as opposed to looping through all the values and comparing each value. This technique may not apply to your data structure and should never be used when LOCATE statements would suffice. And as always, "Know your data."

This code example was found on http://www.pick911.com/indexdcount.html