With thanks to Jonathan Finch, I think I have the problem sorted.
Substring comparisons are the answer (fairly obviously) but the bit I
was missing was the idea of wrapping the string of codes AND the
integer field in commas, to cope with the first and last values in the
string.
Brian.
>
>Hi folks,
>
>I'm trying to help a friend out with writing a query, and it's giving
>me headaches. Can someone point me in the right direction, please?
>
>Simplifying the problem, this is a two table database. The first table
>contains two fields, one is a character description, the other an
>integer code. The character description is what the end user keys in
>to do the search, and it has to work on partial strings.
>
>The main database contains a character field which can contain an
>arbitrary number of these codes concatenated together as a
>comma-separated list - let's say 10 codes maximum, although I can't
>see that matters, and yes, it's possible for this field to be empty.
>These codes are not padded, so the character field could contain
>1,11,21,101 for example, and 1 must only match with 1, not with the
>other three.
>
>What the query needs to do is a SELECT * on the main database where
>the field of concatenated codes contains any one of the zero or more
>codes returned by a subquery on the lookup table.
>
>Can someone point me in the right direction, please? This is testing
>my (very rusty) SQL beyond breaking point. Yes, I know some functions
>can vary across different SQL implementations (the differences between
>Oracle and DB2 used to be a major curse when I was doing this sort of
>thing for a living ~20 years ago) but assume Firebird, I think I'm
>capable of sorting out any differences there may be between Firebird
>and the phone-based database that's actually being used.
>
>Thanks,
>
>Brian.
>
>
>
>
>------------------------------------
>Posted by: brian <brian@...>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Documentation item
>on the main (top) menu. Try FAQ and other links from the left-side menu there.
>
>Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>------------------------------------
>
>Yahoo Groups Links
>
>
>