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

The request to obtain the position of a string within a string [locate] will not function for the given scenario. That is the case even if the compressed codes are always increasing within the string. As a counterexample, if the codes of one, ten, and eleven [where the string to be searched is '011011'], then using a search for '11' with the given logic will not include that row. The logic fails of course, because the first occurrence of '11' will be found with an odd starting position; but it is the second '11' which needs to be found and which starts on an even position.

To get the /position/ to function, the data would better be stored as single characters. Since there are 0x00 to 0xFF characters giving more than double the available 0d00 to 0d99, searching for x'0B' in the binary string [*HEX or CCSID 65535] field would be functional without the addition of the MOD function requirement. However using only /printable/ characters, effectively 0x40 and above, makes the binary string more capable for viewing the string data.

Regards, Chuck

Peter Dow wrote:

How about

SELECT * FROM myFile
WHERE POSITION('01' IN MyCodes) > 0 and MOD(POSITION('01' IN MyCodes), 2) = 0

James Lampert wrote:

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?