Re: FN-FORUM: One for the SQL Experts

date posted 10th September 2007 23:32

Hi Craig,
I think this can be done in SQL using the INSTR() function. Try adding
something like this to your SQL and see if this works:

"AND INSTR(CONCAT(',',mailingGroupID), '," & strSupplierNumber & "') > 0"

I haven't tested this but hopefully it'll work for you.

This basically adds a comma to your mailingGroupID field (so that all
numbers have a preceding comma) and then looks for your supplier number
(with a preceding comma) in this string.

I'm assuming that your mailingGroupID field does not contain any spaces and
that there is a comma before each number. I'm also assuming that the INSTR
function will work this way in MySQL 5, as I usually work with SQL Server or
Oracle.

cheers
Dudley

Dudley Trueman
http://www.truecreations.co.uk


>> Hi,
>>
>> I'm trying to build a query in classic ASP to fire against a MySQL 5.0
>> server that can look inside a field value.
>>
>> So far I have:-
>> strSQL = "SELECT COUNT(mailingListID) AS recount FROM tblMailingList
>> WHERE
>> IsNull(unsubscribe) AND domainReg = '" & strDomain & "'"
>>
>> 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.
>>
>> Would this be a sub-query, can it be done, am I going about it all the
> wrong
>> way?
>>
>> Any advice gratefully received.
>>
>> Craig
>>