On Sat, 30 May 2015 09:12:09 -0400, you wrote: 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 <[email protected]> >------------------------------------ > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > >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 > > >
