RE: SQL searching in a field that has a series of codes

Granted, it's not likely to perform well, but something like this might work
for the given scenario:

SELECT *
FROM myFile
WHERE '01' IN (SUBSTR(code,1,2),SUBSTR(code,3,2),SUBSTR(code,5,2),
SUBSTR(code,7,2))

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL searching in a field that has a series of codes

Postulate a field, containing a series of codes representing multiple
selections of a drop-down list box.

These codes are, for argument's sake, two-digit numbers, and let us
assume that the whole gamut from 00 to 99 is a valid value.

Obviously, if we separate the codes with some delimiter that isn't a
valid part of any code (e.g., a space), SQL can easily tell whether the
field contains any code we're looking for.

But now let us assume that we're being very stingy with space, and
stringing the codes together without any delimiters. In other words, if
our selections are 01, 02, 03, and 04, the field looks like "01020304"
in the file.

Searches for 01, 02, 03, and 04 would all hit, but so would searches for
10, 20, and 30. Is there a practical way to prevent the spurious hits,
other than to bite the bullet and allow space for delimiters?