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

Well there is a brute force kind of way, assuming you want to find a single value and you know how long the field is with the values to find. In this example the field is maybe 4 characters long - if longer, just add more or sub-clauses.

select * from vern/charfile where substr(char4,1,2) = '03' or substr(char4,3,2) = '03'

This could be done nicely with a QMQRY and a replacement variable, as in

select * from vern/charfile where substr(char4,1,2) = &QT&NUM&QT or substr(char4,3,2) = &QT&NUM&QT

And that can be fronted by a CMD and a CLLE very nicely.

That's all I have at the end of the day!

Vern

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?