Dear Kevin:
The answer may be partially to do this:
WHERE "," & db1.serial & "," LIKE "*," & db2.serial & ",*"
If the list is:
A,B,C,something
and you are searching for "something" then it searches for:
,something,
in the list
,A,B,C,something,
That is, it adds the commas to make the search consistent.
However, if db1.serial is entered by a user typing this list, there are
multiple problems:
- users will commonly, but not necessarily consistently, put a space after
the comma. That blows the above method away completely, unless you want to
search for all possible combinations of the space missing or not. That's 4
combinations, and therefore 4 searches.
- this search cannot use indexing, and forces a table scan. With the above,
it may well be 4 table scans. That makes for terrible performance.
The solution is to structure your database with a separate, dependent
(related one-to-many) table containing one row for each of the items listed
in db1.serial. This can then be indexed, and the search can be made just
once and using indexing. Solves the performance problem to the best
possible.
Finally, you should (if possible) keep a list of values that are permitted
in this set. Allow users to choose from this list, adding to it only when
necessary. If you're going to search this, it would be very helpful if you
enforce consistent spelling of the things in the list. Mis-spelled items in
the list, or mis-spelled keys for which you search, these will create
inconsistent results. The search itself should be driven by a list of all
the values used so far.
By the way, do not write [db1.serial] if you mean the column [serial] in
table [db1]. It must be written [db1].[serial] or it can be written
db1.serial. [db1.serial] would refer to a column whose name includes the
period. Not nice!
Tom Ellison
