As far as the database is concerned, those aren't delimited numbers, they're just a long text string that happens to not contain any letters. You could try setting up a full text index on that field and use the match/against search structure. Although you should read up on full text searches since there are a lot of options that will affect your search. I've never tried full text searching on numbers, I don't know if MySQL would consider them words or not.
The other alternative, is a contained in search which is what you are trying to do, but you have the syntax wrong. If you want to search for a string contained within a field, you need to use the LIKE and wild card symbold: %
SELECT * FROM table WHERE field LIKE '%[number]%'
But that search is going to be slow since it can't use any indexes. You also have the problem of substring matching. For instance, searching on '%9%' would find records containing 9, or 99, or 911, or 19, or 293, etc. The only way around that would be to pad you field with a comma (,) at the beginning and end so you could search on '%,9,%'.
On Jun 24, 2004, at 11:11 AM, Eric Scuccimarra wrote:
I have a table where one field is a long list of numbers in comma-delimited format.
I need to do a query like:
SELECT * FROM Table WHERE [number] IN list
If I cut and paste the actual list in it works fine but when I use the column-name containing the list it returns nothing. I've been searching the MySQL docs for hours and haven't turned up anything. Apparently I can use set functions but the lists seem to be too big to store as sets.
Any help is appreciated.
Eric
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
