RE: FN-FORUM: One for the SQL Experts

date posted 11th September 2007 08:45

Craig wrote:
> Hi,
>
> There is a tblMailingList.mailingGroupID field that has recently been
> converted to hold a comma delimited list of mailingGroupID's. I need
> to add something to this query to say "AND the supplied number exists
> in mailingGroupID" but in SQL of course. In other words, if the
> mailingGroupID field holds: 5,2,6,8,2 and the supplied number is 2
> then I want it included in the count.

Firstly the obligatory pedagogic advice - don't store multiple values in one
column like this...ever. It may seem like hard work to create a separate
table but in any halfway normal scenario where the site will continue to be
used and developed it will be less work and certainly less pain in the long
run. Even now that ship has sailed I would seriously consider going back
and reworking it with the extra table.

Assuming you don't do that, your best bet is to use a delimiter and ensure
it goes at _both_ ends of the data e.g.

,1,11,11,12,2,21,

Then you can search explicitly for "," & Number & "," which avoids false
matches on partial numbers.

If for any reason you can't even change the way it is stored, you can
probably do it with a regexp search along the lines of

"(,|^)" & Number & "(,|$)"

Which is to say the number with either a comma or the beginning of the data
before it and either a comma or the end of the data after it.

HTH,

Dai