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

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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?
>
>